Splitting text to multiple columns based on delimiter in SQL server

Question

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

Answer 1

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.