Tag : maxrecursion


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 … More