Split string by two delimiters into two columns

Question

I have a string value which has numeric values separated by comma and then by a pipe. I want to split them into a table with two columns. I could split the string by one delimiter but unfortunately couldn't find a way to split by two. Please help.

DECLARE @list NVARCHAR(MAX) = '1,101|2,202|3,303';

The result should be like below.

1   101
2   202
3   303

Thanks in advance.

Answer 1

If you're using SQL Server 2016 or Azure, you have access to the new SPLIT_STRING function. If not I recommend using Jeff Moden's DelimitedSplit8K function, which is widely regarded as the fastest, most efficient SQL based string splitter available...

DECLARE @list NVARCHAR(MAX) = '1,101|2,202|3,303';

SELECT 
    Col1 = LEFT(dsk.Item, sl.SplitLocation - 1),
    Col2 = SUBSTRING(dsk.Item, sl.SplitLocation + 1, LEN(dsk.Item))
FROM 
    dbo.DelimitedSplit8K(@list, '|') dsk    -- code for DelimitedSplit8K can be found here... http://www.sqlservercentral.com/articles/Tally+Table/72993/
    CROSS APPLY ( VALUES (ISNULL(NULLIF(CHARINDEX(',', dsk.Item, 1), 0), 1)) ) sl (SplitLocation);