CODEWITHSUNDEEP
sqlsql-server
bennett
bennett

Reputation: 31

SQL Server - Splitting a string

I have the below string which is currently in one column, called Column1, in a table called dbo.faresnumb

Column1
512.65USD/52.65USD/0167025354825

I want to create three new columns from this string called Fare1, Fare2, Number1 like below

Fare1      Fare2      Number1
512.65USD  52.65USD   0167025354825

So the delimter is the / and the character count will not always be the exact same. For example, Fare1 could be 54.00USD rather than 512.65USD and I still need to capture everything before the first /. I know is SAS there is a way to scan a substring to find a delimiter and assign new variables, but I am new to SQL. Any help would be much appreciated!

Thanks, Bennett

Upvotes: 0

Views: 74

Answers (2)

Abhishek
Abhishek

Reputation: 2490

One more way to achieve the same using a bit of XML - 

select cast(concat('<x>', REPLACE(Column1, '/', '</x><x>'), '</x>') as xml).value('/x[1]','varchar(100)'), 
       cast(concat('<x>', REPLACE(Column1, '/', '</x><x>'), '</x>') as xml).value('/x[2]','varchar(100)'),
       cast(concat('<x>', REPLACE(Column1, '/', '</x><x>'), '</x>') as xml).value('/x[3]','varchar(100)') from dbo.faresnumb

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Such string operations are a pain in SQL Server. You can do:

select parts.part1 as fare1,
       left(rest, charindex('/', rest) - 1) as fare2,
       stuff(rest, 1, charindex('/', rest), '') as number1
from t cross apply
     (values (left(col, charindex('/', col) - 1),
              stuff(col, 1, charindex('/', col), '')
             )
     ) parts(part1, rest);

Here is a rextester.

Sadly, SQL Server 2016 offers split_string() which makes this simpler. The sad part is that it does not return the position of the substring in the string. That is needed to ensure that the three extracted values are in the right order.

Upvotes: 2

Related Questions