sql server 2008中的子字符串 [英] substring in sql server 2008

问题描述

来自下表

 date                description                                  amount
    29/12/13      <13363000054123>JIT BAHADUR LAMICHHANE     CR 10,000.00  TBI 29/12/13 29/12/13
    29/12/13      <13363740800138>MAN BAHADUR .              CR 1,19,595.00  TBI 29/12/13 29/12/13
    29/12/13      <555349302906>CHANDRA PRASAD DAHAL         CR 24,054.30  TBI 29/12/13 29/12/13
    29/12/13      <13362144250203>BISHNU GURUNG DHAN         CR 1,30,562.00  TBI 29/12/13 29/12/13

我需要以下记录

date               description           amount 
29/12/13      <13363000054123>   CR 10,000.00 
29/12/13      <13363740800138>   CR 1,19,595.00  

我尝试了 substring ，但字符串大小不能固定在 'amount' 列上.最好的方法是什么

I tried substring , but string size cannot be fixed on 'amount' column. What is the best way

推荐答案

尝试 CHARINDEX 和 SUBSTRING 的组合.假设金额格式为xxx.xx"

Try the combination of CHARINDEX and SUBSTRING. Assuming amount has format 'xxx.xx'

SELECT date, 
       LEFT(description, Charindex ('>', description)), 
       LEFT(amount, Charindex ('.', amount)) 
       + Substring(amount, Charindex ('.', amt)+1, 2) 
FROM   tbl 

这篇关于sql server 2008中的子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！