Recursive SQL CTE to split CSV string to table rows

DECLARE @strCSV VARCHAR(8000) , @delimiter CHAR(1) SET @delimiter = ',' SET @strCSV = '001002330 ,001012657 ,001046228 ,001105047 ,001138189' DECLARE @TableVariable TABLE ( ID INT, Item VARCHAR(12) ) ;WITH RegNumStartEndIndexes AS( SELECT StartIndex = 0 , EndIndex = CHARINDEX(@delimiter,@strCSV) UNION ALL SELECT EndIndex+1 , CHARINDEX(@delimiter,@strCSV,EndIndex+1) FROM RegNumStartEndIndexes WHERE EndIndex>0 ) INSERT INTO @TableVariable (ID, Item) SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 1)) AS ID , REPLACE( REPLACE( SUBSTRING(@strCSV, StartIndex, COALESCE(NULLIF(EndIndex,0), LEN(@strCSV)+1)-StartIndex), CHAR(13), ''), CHAR(10), '') AS Item FROM RegNumStartEndIndexes option (maxrecursion 0) -- [option (maxrecursion 0)] added To avoid following error when number of recursions gets high -- The maximum recursion 100 has been exhausted before statement completion SELECT * FROM @TableVariable

Originally grabbed the idea from here

Recursive SQL CTE to split CSV string to table rows