使用遞迴CTE拆解用特定間隔符號所組成的字串
實務上遇到需將前端AP的CheckBox勾選狀況以逗點或其他特定間格符號串起來後儲存於TABLE中某個欄位時，只要以迴圈方式檢查每個CheckBox的Checked屬性，就可以串成”1,0,1,0,0”的字串，如下圖：
若要將”1,0,1,0,0”拆解成下列型態，除了以迴圈方式尋找分隔符號外，可以使用遞迴CTE計算每個分隔符號的位置，再跟原始資料做join後以substring取出資料。
作法如下：
- 先宣告遞迴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
參考資料：T-SQL 問題解決實戰，楊志強，旗標