使用遞迴CTE拆解用特定間隔符號所組成的字串

• 先宣告遞迴CTE計算每個分隔符號在欄位中的位置。

`declare @splitter varchar(10) = ','`
`declare @splitterlength int = len(@splitter) + 1`

`;with Split as`
`(`
` select c1, 1 as pos, 1 as startidx`
`        ,charindex(@splitter,c2 + @splitter) -1 as  endidx`
` from @t`
`where len(c2) > 0`
`union all`
`select s.c1,s.pos + 1, s.endidx + @splitterlength`
`        ,charindex(@splitter,t.c2 + @splitter,s.endidx +2) -1`
`from Split s`
`join @t t`
`on s.c1 = t.c1`
`and charindex(@splitter,t.c2 + @splitter, s.endidx + 2) > 0`
`)`
• 原始資料跟CTE做JOIN後使用SubString拆解字串。
`select t.c1,pos,substring(t.c2,startidx,endidx-startidx+1) as content`
`from @t t`
`join Split s`
`on t.c1 = s.c1`
`order by c1,pos`

• 完整程式碼如下：
`declare @t table`
`(c1 varchar(10)`
`,c2 varchar(50)`
`)`

`insert into @t values ('A','1,1,0,0,1'),('B','1,0,0,1')`

`declare @splitter varchar(10) = ','`
`declare @splitterlength int = len(@splitter) + 1`

`;with Split as`
`(`
` select c1, 1 as pos, 1 as startidx`
`        ,charindex(@splitter,c2 + @splitter) -1 as  endidx`
` from @t`
`where len(c2) > 0`
`union all`
`select s.c1,s.pos + 1, s.endidx + @splitterlength`
`        ,charindex(@splitter,t.c2 + @splitter,s.endidx +2) -1`
`from Split s`
`join @t t`
`on s.c1 = t.c1`
`and charindex(@splitter,t.c2 + @splitter, s.endidx + 2) > 0`
`)`

`select t.c1,pos,substring(t.c2,startidx,endidx-startidx+1) as content`
`from @t t`
`join Split s`
`on t.c1 = s.c1`
`order by c1,pos`