Split array element in a column

I have worked on solutions for this in this posting: http://jingyang.spaces.live.com/blog/cns!CC21A118B1B5250!216.entry
I revisit this topic today and read Itzik’s Inside SQL Server book again. I took a note for the solutions he presented in his book for future reference.

declare @t table (id int, id2 int, Col1 varchar(50))

INSERT

INTO @t

SELECT

1, 1, ‘11,222,1,3,21,1,2’

UNION

ALL SELECT 2,2,‘9,7,9’

UNION

ALL SELECT 3,3,‘4,5,6,3,1,4,5’

UNION

ALL SELECT 4,4,‘141,5,6,31,1,42,5’

–SolutionÂ 1

;

WITH myCTESplit

AS

(

SELECT

id,id2, 1 as pos, 1 as startpos, charindex(‘,’,Col1+‘,’)-1 as endpos

FROM

@t

WHERE

LEN(Col1)>0

UNION ALL

SELECT

Prv.id,Prv.id2,Prv.pos+1,Prv.endpos+2, charindex(‘,’,Cur.Col1+‘,’,Prv.endpos+2)-1

FROM

myCTESplit as Prv JOIN @t as Cur

ON

Cur.id=Prv.id AND charindex(‘,’,Cur.Col1+‘,’,Prv.endpos+2)>0

)

SELECT

a.id,a.id2, pos,SUBSTRING(Col1,startpos,endposstartpos+1) as element FROM @t a

JOIN

myCTESplit as s ON s.id=a.id

ORDER BY id,pos

— Solution 2

;

WITH NumsCTE

AS

(

SELECT

1 as n

UNION

ALL

SELECT n+1 FROM NumsCTE WHERE n<100

)

SELECT

id,id2,nlen(REPLACE(LEFT(Col1,n),‘,’,))+1 as pos,

SUBSTRING

(Col1,n,charindex(‘,’,Col1+‘,’,n)-n) as element

FROM

@t JOIN NumsCTE ON n<=len(Col1) AND SUBSTRING(‘,’+Col1,n,1)=‘,’

ORDER

BY id,id2,pos