sql server 2008中的子字符串

作者: OLDSUMMER大海
来源: 51数据库
2023-02-07

问题描述

来自下表

 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

推荐阅读