Oct 17, 2006

Alternative approach to splitting a comma separated list

I had already blogged on how we can split a comma separated list using CTEs in my other post on Interesting queries using recursive Common Table Expressions , the idea of which was a slight modification to the one conceived and presented by Erland Sommarskog in Arrays and Lists in SQL Server.

After reading through the blog of Hugo Kornelis on
using stuff for string manipulation, I realised that the STUFF function, indeed, made splitting a comma separated values string (CSV) pretty much easy.

Given below is a query (using CTEs in SQL Server 2005) which (according to me) is much more simpler than the version I had presented before, in splitting a CSV.

declare @a varchar(100)
set @a = 'A,B,C,D,E,F,G,H,IJ,KL,MN,OP,WER,TRY,QQR';
with tbl_for_csv as
select left(@a, charindex(',',@a) - 1) as val,
stuff(@a +',',1,charindex(',',@a),'') as col
union all
select cast(left(col, charindex(',',col) - 1) as varchar(100)),
stuff(col,1,charindex(',',col),'') from tbl_for_csv
where col <> ''
select * from tbl_for_csv

You can run the query and deduce the logic from the result. Like Hugo said, Its much more easier if you just stuff it :)


Post a Comment