CODEWITHSUNDEEP
sqlsql-server
Salman81
Salman81

Reputation: 145

Having trouble while split alphanumeric string

I am using SQL Server 2016, and I have a table A

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

I need an output as shown here:

ID            Comments              Amount
------------------------------------------
1             John                  123
2             Bret                   45
3             HELEN                 150
4             NULL                  NULL

Code is here, but I get an error

Invalid length parameter is passed to left or substring

Code:

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)

Thanks in advance

Upvotes: 1

Views: 104

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

Just another option is to use a bit of JSON

Example or dbFiddle

Select A.ID 
      ,Comments= JSON_VALUE(S,'$[0]')
      ,Amount  = JSON_VALUE(S,'$[1]')
 From YourTable A
 Cross Apply ( values ( '["'+replace(replace(replace([Comments],'-',' '),'$',' '),' ','","')+'"]' ) ) B(S)

Results

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

Upvotes: 2

Related Questions