CODEWITHSUNDEEP
sql
Salman81
Salman81

Reputation: 145

Trying to split string with alphanumeric

TABLE A

ID            Comments
1             John $123
2             Bret $45 33-4ABC
3             HELEN $150-KLR

I need output like below

ID            Comments              Amount
1             John                  $123
2             Bret                  $45
3             HELEN                 $150

Here is my code . my code Give me Amount of bret $45 33-4ABC and helen Amount $150-KLR that really not want. it should be $45 and $150. in other words any space or (-) after $45 or $150 etc has to off.

Thanks In Advance

SELECT id,

REPLACE(Comments,SUBSTRING(Comments,
  PATINDEX('%[$,0-9]%',Comments),
  Len(Comments)),'') AS Comments,
  SUBSTRING(Comments,PATINDEX('%[$,0-9]%',Comments),
  LEN(Comments)) As Amount

from table A

Upvotes: 0

Views: 286

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Assuming you are using SQL Server, this works on the data you provided:

select a.*, v.comments, left(v.rest, patindex('%[^$0-9]%', rest + ' ') - 1) as amount
from a cross apply
     (values (rtrim(left(comments, charindex('$', comments + '$') - 1)),
              stuff(comments, 1, charindex('$', comments + '$') - 1, '')
             )
     ) v(comments, rest);

Here is a db<>fiddle.

Upvotes: 1

Related Questions