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

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

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

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,

• Gopal Krishna Ranjan Post author

Thanks for your comment “Reena”, i have made the changes and now it’s working properly. The problem was in casting to varchar. I have made it to VARCHAR(MAX).