Split two comma-separated values by comma delimeter and store in two columns of same temporary table

Question

Again I need your help in writing a SQL query.

I have two comma-separated strings:

1,2,3     and      a,b,c

Number of elements (e.g. 1, 2 & 3) in both the comma-separated values are the same. In the given example, its 3.

What I want is to split the comma-separated values by comma delimeter and store them in two columns of same temporary table.

enter image description here

I am using SQL Server 2012.

possible duplicate of [Split function equivalent in T-SQL?](http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) — Yannick Meeus, Mar 02 '15 at 08:04
No, I need two comma separated values into two columns of same temporary table. Both questions are different. — Kartic, Mar 02 '15 at 08:05
If you have a look at the article linked in that post, there's an example of how to do string splitting in SQL. The algorithm is there, you just have to apply it to two different strings, aggregate the results and store in a temp table. — Yannick Meeus, Mar 02 '15 at 08:08

score 2 · Answer 1 · answered Mar 02 '15 at 10:35

I would suggest you to create an UDF which accepts the delimited string input and returns a table with ID, something like below (so this can be reused where required):

Go
CREATE FUNCTION [dbo].[ReturnTableFromDelimetedStringWithID] (@list VARCHAR(MAX), @delimiter VARCHAR(30), @inclblanks BIT) 
    RETURNS @table TABLE (id INT NOT NULL PRIMARY KEY, item VARCHAR(3000) ) 
    AS 
    BEGIN 
        DECLARE @item    VARCHAR(3000) 
        DECLARE @delim      VARCHAR(30) 
        DECLARE @str        VARCHAR(MAX) 
        DECLARE @pos        INT 

        DECLARE @id        INT 

        SET @id = 0 
        SET @delim = @delimiter 
        IF @delim='' SET @delim=',' 

        SET @str = @list + @delim

        WHILE CHARINDEX (@delim, @str) > 0
        BEGIN

            SET @id = @id + 1 
            SET @pos = CHARINDEX (@delim, @str)
            SET @item = CONVERT(VARCHAR(3000), SUBSTRING( @str, 1, @pos - 1))
            SET @str = SUBSTRING( @str, @pos + LEN(@delim), LEN( @str))

            IF @inclblanks=1 
                INSERT INTO @table VALUES (@id, @item) 
            ELSE 
                IF @item<>'' INSERT INTO @table VALUES (@id, @item) 

        END 

        RETURN 
    END
GO

Then, you can query like below:

DECLARE @str1 VARCHAR(MAX) = '1,2,3'
DECLARE @str2 VARCHAR(MAX)= 'a,b,c'
DECLARE @delimiter CHAR(1) = ','
DECLARE @TmpTbl3 TABLE (Col1 VARCHAR(50), Col2 VARCHAR(50))

INSERT INTO @TmpTbl3
SELECT t1.c2, t2.c2
FROM 
(
    SELECT id [c1], item [c2] FROM dbo.ReturnTableFromDelimetedStringWithID(@str1, ',', 0)
) as t1
INNER JOIN 
(
SELECT id [c1], item [c2] FROM dbo.ReturnTableFromDelimetedStringWithID(@str2, ',', 0)
) AS t2 ON t1.c1=t2.c1

--SELECT * FROM @TmpTbl3

This would probably be the best way.

score 1 · Accepted Answer · answered Mar 02 '15 at 08:25

I have figured out the solution. But I would like to post it so that it could be helpful to other people or you can guide me to make it more effective -

DECLARE @xml1 xml
DECLARE @xml2 xml
DECLARE @str1 VARCHAR(MAX)
DECLARE @str2 VARCHAR(MAX)
DECLARE @delimiter CHAR(1)
DECLARE @TmpTbl1 TABLE (Id INT IDENTITY(1,1), Col1 VARCHAR(50))
DECLARE @TmpTbl2 TABLE (Id INT IDENTITY(1,1), Col2 VARCHAR(50))
DECLARE @TmpTbl3 TABLE (Col1 VARCHAR(50), Col2 VARCHAR(50))

SET @str1 = '1,2,3'
SET @str2 = 'a,b,c'
SET @delimiter = ','

SET @xml1 = CAST(('<X>'+replace(@str1, @delimiter, '</X><X>')+'</X>') AS XML)
SET @xml2 = CAST(('<X>'+replace(@str2, @delimiter, '</X><X>')+'</X>') AS XML)

INSERT INTO @TmpTbl1 (Col1)
SELECT C.value('.', 'VARCHAR(50)') AS value FROM @xml1.nodes('X') AS X(C)

INSERT INTO @TmpTbl2 (Col2)
SELECT C.value('.', 'VARCHAR(50)') AS value FROM @xml2.nodes('X') AS X(C)

INSERT INTO @TmpTbl3 (Col1, Col2)
SELECT tmp1.Col1, tmp2.Col2
FROM @TmpTbl1 tmp1
INNER JOIN @TmpTbl2 tmp2 ON tmp1.Id = tmp2.Id

SELECT Col1, Col2 FROM @TmpTbl3

Split two comma-separated values by comma delimeter and store in two columns of same temporary table

2 Answers2