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