/* 運用Recursive SQL將以逗號組出之字串拆解成多筆資料
ex: 將a,bb,ccc,d,e,f拆解開來
*/
WITH N(COL,TXT,TXT1) AS (
SELECT 1 COL,TXT,TXT
FROM (SELECT ','||'a,bb,ccc,d,e,f' || ',' TXT
FROM SYSIBM.SYSDUMMY1) T
UNION ALL
SELECT N.COL + 1,TXT,SUBSTR(TXT1,LOCATE(',',TXT1,2))
FROM N
WHERE N.COL + 1 <= LENGTH(N.TXT) - LENGTH(REPLACE(N.TXT,',','')) -1
)
SELECT SUBSTR(TXT1,2,LOCATE(',',TXT1,2) -2)
FROM N;
得到以下結果
a
bb
ccc
d
e
f
