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 has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.


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

  • 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.

  • 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’, ‘,’)

  • 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,