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

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.


Leave a Reply to Reena Singh Cancel reply

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

*

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,