substring in sql server 2008

Question

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

Sorry, on what condition, you filter the rows? – Maheswaran Ravisankar Jan 02 '14 at 07:39 — Maheswaran Ravisankar, Jan 02 '14 at 07:39

score 2 · Answer 1 · answered Jan 02 '14 at 07:53

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))

answered Jan 02 '14 at 07:53

Jaques

2,215
1
18
35

TBI is not fixed it may have abc,xyz anything – Jan 02 '14 at 08:01

score 1 · Accepted Answer · edited Jan 02 '14 at 08:37

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

edited Jan 02 '14 at 08:37

Hawk

5,060
12
49
74

answered Jan 02 '14 at 07:54

sumit

15,003
12
69
110

score 0 · Answer 3 · edited May 23 '17 at 10:25

0

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.

edited May 23 '17 at 10:25

Community

1
1

answered Jan 02 '14 at 07:39

Thomas B. Lze

1,070
7
14

1

While I agree the schema is questionable at best, what's the value for the OP (or people who find this answer later from a search) in saying that it is quite wrong, but not explaining why or giving an example of how to make it better? – Tim Jan 02 '14 at 07:50
You're right.I thought about schema normalization : [link]http://en.wikipedia.org/wiki/Database_normalization The real amount should be alone in a column ('10 000.00' instead of 'CR 10 000.00' for example) – Thomas B. Lze Jan 02 '14 at 07:53

score 0 · Answer 4 · answered Jan 02 '14 at 09:33

0

try this for description column.

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

answered Jan 02 '14 at 09:33

The Hill Boy

162
7

substring in sql server 2008

4 Answers4

Linked