Convert a comma separated list into table 4


In this post, we are going to learn “how we can convert a comma separated list into a table using a recursive CTE”. Below is the code:


DECLARE @vc_CommaSepValues VARCHAR(1000)
SET @vc_CommaSepValues = '1,R,TYF,776876,GHGF,6856,TYFT,1,R,TYF,776876,GHGF,6856,TYFT,1,R,TYF,776876,GHGF,6856,TYFT,1'

;WITH CTE AS
(
SELECT CAST(LEFT(@vc_CommaSepValues, CHARINDEX(',', @vc_CommaSepValues + ',') - 1) AS VARCHAR(MAX)) AS Val
,CAST(STUFF(@vc_CommaSepValues, 1, CHARINDEX(',', @vc_CommaSepValues + ','), '') AS VARCHAR(MAX)) AS RecVal
UNION ALL
SELECT CAST(LEFT(RecVal, CHARINDEX(',', RecVal + ',') - 1) AS VARCHAR(MAX)) AS Val
,CAST(STUFF(RecVal, 1, CHARINDEX(',', RecVal + ','), '') AS VARCHAR(MAX)) AS RecVal FROM CTE
WHERE RecVal > ''
)

SELECT Val FROM CTE
OPTION(MAXRECURSION 32767)

Why OPTION MAXRECURSION?

Hint OPTION(MAXRECURSION 32767) is a query hint which instructs to compiler to iterate 32767 times. Default, max recursion of a loop in SQL is 100 and in case we will not put this hint at the end, any string which requires more loop than 100 will raise an error.
To avoid the error we have used OPTION(MAXRECURSION 32767).

Thanks for reading.

Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal is a passionate Data Engineer and Data Analyst. He has implemented many end to end solutions using Big Data, Machine Learning, OLAP, OLTP, and cloud technologies. He loves to share his experience at https://www.sqlrelease.com/. Connect with Gopal on LinkedIn at https://www.linkedin.com/in/ergkranjan/.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 thoughts on “Convert a comma separated list into table

  • Avatar
    john

    Not to steal anyones thunder here but there is a much faster better way of doing this, Also this is not my script i modified another that was posted by Jeff Moden from here http://www.sqlservercentral.com/articles/Tally+Table/72993/ I suggest reading his article as it is very enlightening on methods for splitting a column by a delimiter. Plus he shows the test results from various ways to achieve this, ending with his 8000 char splitter which is the one i posted below.

    CREATE FUNCTION [dbo].[Explode](@pString VARCHAR(8000) ,@pDelimiter CHAR(1))
    RETURNS TABLE
    WITH SCHEMABINDING
    AS
    RETURN
    WITH E1 ( N )
    AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    ),
    E2 ( N )
    AS ( SELECT 1 FROM E1 a , E1 b ),
    E4 ( N )
    AS ( SELECT 1 FROM E2 a , E2 b),
    cteTally ( N )
    AS (SELECT TOP ( ISNULL(DATALENGTH(@pString), 0) ) ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL) )
    FROM E4),
    cteStart ( N1 )
    AS (SELECT 1
    UNION ALL
    SELECT t.N + 1
    FROM cteTally t
    WHERE SUBSTRING(@pString, t.N, 1) = @pDelimiter ),
    cteLen ( N1, L1 )
    AS ( SELECT s.N1 , ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1), 0) – s.N1, 8000) FROM cteStart s)
    –do the split
    SELECT listid = ROW_NUMBER() OVER ( ORDER BY l.N1 ) ,
    listvalue = SUBSTRING(@pString, l.N1, l.L1)
    FROM cteLen l;
    GO

    this method will return much faster results than most other methods of doing this and almost keeps up with a clr splitter which really is the best way to go if you need to do this sort of thing often.

  • Avatar
    Reena Singh

    we can use below code for the same, it works correctly…

    CREATE FUNCTION [dbo].[Split]
    (@String varchar(8000), @Delimiter char(1))
    returns @temptable TABLE (items varchar(8000))
    as
    begin
    declare @idx int
    declare @slice varchar(8000)
    select @idx = 1
    if len(@String)0)
    insert into @temptable(Items) values(@slice)
    set @String = right(@String,len(@String) – @idx)
    if len(@String) = 0 break
    end
    return
    end

    Test it….

    DECLARE @vc_CommaSepValues VARCHAR(8000)
    SET @vc_CommaSepValues = ‘thjio,fxg,jkoj,xcfvx,8,5,1,R,TYF,776876,GHGF,6856,TYFT,ggg,hyhj,hnn’

    select * from [dbo].[Split](‘thjio,fxg,jkoj,xcfvx,8,5,1,R,TYF,776876,GHGF,6856,TYFT,ggg,hyhj,hnn’, ‘,’)

  • Avatar
    Reena Singh

    It is not working correctly when we set

    SET @vc_CommaSepValues = ‘1,R,TYF,776876,GHGF,6856,TYFT,1,R,TYF,776876,GHGF,6856,TYFT,1,R,TYF,776876,GHGF,