CODEWITHSUNDEEP
sql-server-2012
Kartic
Kartic

Reputation: 2985

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

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.

Upvotes: 1

Views: 895

Answers (2)

Sathish
Sathish

Reputation: 2066

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.

Upvotes: 2

Kartic
Kartic

Reputation: 2985

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

Upvotes: 1

Related Questions