substring in sql server 2008

问题:

From the table below

 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

I need the records as below

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

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

回答1:

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 

回答2:

This produce the information you have stipulated.

Declare @S varchar(50)
Declare @T varchar(50)
Select @S = '<13363000054123>JIT BAHADUR LAMICHHANE', @T = 'CR 10,000.00  TBI 29/12/13 29/12/13'
Select SUBSTRING(@S, 1, PATINDEX('%>%', @S)), SUBSTRING(@T, 1, PATINDEX('% TBI%', @T))

Use this in your query

Select date, SUBSTRING(description, 1, PATINDEX('%>%', description)), SUBSTRING(amount, 1, PATINDEX('% TBI%', amount))

回答3:

You can use this function to split the amount. But your schema is quite wrong, you should work on it to avoid theses kind of problems.

回答4:

try this for description column.

SELECT 
    date,
    SUBSTRING(description, 0,charindex('>',description)+1)  as description      
from    
    @table