sql server 2008中的子字符串

2021-09-10 00:00:00 sql tsql sql-server

来自下表

 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

相关文章