Set based splitting of delimited strings to columns
I recently presented on using CLR in SQL at DevWeek and
commented on how TSQL Udfs just don't perform. A common scenario people in the
audience were using TSQL Udfs for was splitting delimited strings.
One guy asked me afterwards what the best way to do it was, he had addresses
in one field he wanted to split into x columns. On the spot I cam up with the
following with uses a number of well know tehniques.
This uses the nuber table solution to split the delimited string into rows
and then uses pivot to along with row_number to position the string tokens into
columns. It also uses a cross join to have the process work for each row in the
table.
Note: this is for a num table starting at 0.
declare @m table (id
int, d varchar(100))
insert into
@m values
(1,'simon,sabin,sql')
insert into
@m values
(2,'steve,Jones,data')
select p.*
from (
select id
,
substring(d, start+2, endPos-Start-2)
token
,
row_number() over(partition by id
order
by start) n
from (
select id
, d
, n start
, charindex(',',d,n+2)
endPos
from num
cross join
(select
id
,
',' + d +',' d
from
@m)
m
where n < len(d)-1
and substring(d,n+1,1) = ',')d
)
pvt
Pivot (
max(token)for n in
([1],[2],[3],[4]))p
The great thing here is it works for multiple rows in a table and so you can
replace @m with your table of choice.
You have fixed the number of columns so if you want this to be dynamic then
its more complex..
I was going to wait on posting this but thought it would follow on well from
Madhivanan's post http://sqlblogcasts.com/blogs/madhivanan/archive/2008/03/15/splitting-csv-to-columns.aspx
-