Recursive SQL to split CSV into table rows

Question

After working on another issue here in SO, I came across recursive CTEs that on the surface would look like a pretty simple way to solve the "Split csv into tables" problem.

I put together this example

DECLARE @InputString varchar(255) = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z' SELECT @InputString = @InputString + ',' ; with MyCTE(x,y) as ( SELECT x = SUBSTRING(@InputString,0,PATINDEX('%,%',@InputString)), y = SUBSTRING(@InputString,PATINDEX('%,%',@InputString)+1,LEN(@InputString)) UNION ALL SELECT x = SUBSTRING(y,0,PATINDEX('%,%',y)), y = SUBSTRING(y,PATINDEX('%,%',y)+1,LEN(y)) FROM MyCTE WHERE SUBSTRING(y,PATINDEX('%,%',y)+1,LEN(y)) <> '' OR SUBSTRING(y,0,PATINDEX('%,%',y)) <> '' ) SELECT x FROM MyCTE OPTION (MAXRECURSION 2000); GO 

Is this a really bad idea? What is the overhead in SQL for recursive queries like this, and what are the potential problems for this approach.

By the way, I think this idea / technique could probably be used to solve this other issue.

Answer 1

This one will work with empty lines.

 DECLARE @InputString VARCHAR(1000) SELECT @InputString = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,,1' SELECT SUBSTRING(',' + @InputString + ',', Number + 1, CHARINDEX(',', ',' + @InputString + ',', Number + 1) - Number -1)AS VALUE FROM master..spt_values WHERE type = 'p' AND Number <= LEN(',' + @InputString + ',') - 1 AND SUBSTRING(',' + @InputString + ',', Number, 1) = ',' GO 

Also check out the comments here: S plit string in SQL Server 2005+ CLR and T-SQL for some other ideas

Answer 2

Although it’s too late, and OP has the accepted answer, it’s worth mentioning to read the article on Split Function in Sql Server using the Set base approach , where the author showed many ways to achieve the same.