SQL Server - Splitting a string

Question

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

I think at least you should google it before posting

D Ie
– D Ie

2018-02-16 13:06:04 +00:00
Commented Feb 16, 2018 at 13:06 — D Ie
– D Ie, Commented Feb 16, 2018 at 13:06

Gordon Linoff · Accepted Answer · 2018-02-16 12:53:29Z

2

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.

answered Feb 16, 2018 at 12:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thom A Over a year ago

It surprises me MS haven't added ordinal position yet. The Connect items have had a lot of upvotes, and it would be extremely useful for so many.

bennett Over a year ago

Thank you for your suggestion!! I appreciate it!

Abhishek · Accepted Answer · 2018-02-16 13:30:02Z

1

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

edited Feb 16, 2018 at 13:30

answered Feb 16, 2018 at 13:22

Abhishek

2,4901 gold badge24 silver badges35 bronze badges

1 Comment

bennett Over a year ago

Thank you for your suggestion!! Much appreciated!

Collectives™ on Stack Overflow

SQL Server - Splitting a string

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related