CODEWITHSUNDEEP
sqlsql-servert-sql
sridhar7
sridhar7

Reputation: 36

Splitting text to multiple columns based on delimiter in SQL server

I'm trying to load data from a stage table to final table in SQL server. My column delimiter is a pipe character "|". But I'm getting pipe characters in the text column as shown below.

My data in the stage table is as shown below:

1233|"abcd,edfg"|asdf|3456

1234|xyz|"abnd|tfgt"|8765

I'm trying to write a scalar function which take split the contents of the stage table into multiple columns based on pipe as delimiter.

Desired Output should be:

col_1 col_2 col_3 col_4
1233 "abcd|edfg" asdf 3456
1234 xyz "abnd|tfgt" 8765

If I'm trying to split this using substring and charindex functions in SQL server, I end up splitting "abcd" and edfg into 2 separate columns there by causing column shifting issue.

What would be the best possible way to achieve this?

Upvotes: 1

Views: 1469

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you know there are always two values, just use string functions:

select s.*,
       left(col_2, charindex('|', col_2 + '|') - 1) as col_2_left,
       stuff(col_2, 1, charindex('|', col_2 + '|'), '') as col_2_right
from staging s;

You would need to repeat this for each column that could have duplicates.

Here is a db<>fiddle.

Upvotes: 1

Related Questions