Split a row into two separators into two columns

Question

I have a string value that has numeric values ​​separated by a comma and then by channel. I want to split them into a table with two columns. I could split the string into one separator, but unfortunately could not find a way to split into two. Please, help.

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

The result should look like this.

 1 101 2 202 3 303 

Thanks in advance.

Answer 1

If you are using SQL Server 2016 or Azure, you have access to the new SPLIT_STRING function. If not, I recommend using the Jeff Moden DelimitedSplit8K function, which is widely regarded as the fastest and most efficient SQL-based line splitter ...

 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); 

Answer 2

 CREATE FUNCTION [dbo].[fn_Split_char](@text nvarchar(max), @delimiter varchar(20) = ' ') RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value nvarchar(max) ) AS BEGIN DECLARE @index int SET @index = -1 WHILE (LEN(@text) > 0) BEGIN SET @index = CHARINDEX(@delimiter , @text) IF (@index = 0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index > 1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) SET @text = RIGHT(@text, (LEN(@text) - @index)) END ELSE SET @text = RIGHT(@text, (LEN(@text) - @index)) END RETURN END Select LEFT(value, Charindex(',', value) - 1) , RIGHT(value, Charindex(',', Reverse(value)) - 1) , * from [fn_Split_char] ('1,101|2,202|3,303', '|') 

Answer 3

Use the xml and cross apply path to create multiple rows for one row based on the channel separator, and then use the wrt substring for commas to get the two columns you need

 Create table #temp(list nvarchar(max)) Insert into #temp values('1,101|2,202|3,303') SELECT Substring(Tbl.Col.value('./text()[1]','varchar(50)'),1,1)as col1, Substring(Tbl.Col.value('./text()[1]','varchar(50)'),charindex(',',Tbl.Col.value('./text()[1]','varchar(50)'),1)+1,len(Tbl.Col.value('./text()[1]','varchar(50)'))) FROM (Select cast('<a>'+ replace((SELECT list As [*] FOR XML PATH ('')), '|', '</a><a>') + '</a>' as xml)as t from #temp) tl Cross apply tl.t.nodes('/a') AS Tbl(Col) 

Answer 4

Try using this table function, insert this SP in your main SP

 ALTER FUNCTION [dbo].[delimiter] ( @PARAM_IDS AS VARCHAR(MAX) @PARAM_DELIMITER AS CHAR(1) ) RETURNS @NEW_TABLE TABLE ( NUM INT NOT NULL IDENTITY, ID INT NOT NULL ) AS BEGIN DECLARE @NEXTSTRING AS NVARCHAR(MAX); DECLARE @POS AS INT; DECLARE @STRING AS NVARCHAR(MAX); DECLARE @DELIMITER AS NVARCHAR(MAX); SET @STRING = @PARAM_IDS; SET @DELIMITER = @PARAM_DELIMITER; SET @STRING = @STRING + @DELIMITER; SET @POS = CHARINDEX(@DELIMITER,@STRING); WHILE (@POS <> 0) BEGIN SET @NEXTSTRING = SUBSTRING(@STRING,1,@POS - 1); INSERT @NEW_TABLE (ID) VALUES (@NEXTSTRING); SET @STRING = SUBSTRING(@STRING,@POS+1,len(@STRING)); SET @POS = CHARINDEX(@DELIMITER,@STRING); END RETURN END 

then an example of use

 SET @DETAILS_COUNT = (SELECT COUNT(*) FROM delimiter(@PARAM_MS_UNIT_ID, @DELIMITER));