SQL> select * from t;A B ---------- ------------------------------ A B,C,D,E,F,GSQL> Select a, 2 Substr(b || ',', Instr(b, ',', 1, b.Rid) + 1, 3 Instr(',' || b || ',', ',', 1, b.Rid + 1) - 1 - Instr(',' || b || ',', ',', 1, b.Rid)) b 4 From t, 5 (Select Rownum Rid 6 From Dual 7 Connect By Rownum <= (Select Length(Replace(Translate(b , Replace(b, ',', ''), ' '), ' ', '')) From t) + 1) b;A B ---------- ------------------------------ A C A D A E A F A G A B已选择6行。
表c有两列a,b 假设b最多由100个值拼接 下面是拆分语句 select a,substr(b,instr(','||b,',',1,rn),instr(','||b||',',',',1,rn+1)-instr(','||b||',',',',1,rn)-1),rn from c,(select rownum rn from dual connect by rownum<100) where instr(b||',',',',1,rn)>0 order by a,rn 这里的100是一个常量,如果拼接个数较多,可以用更大的数代替。
---------- ------------------------------
A B,C,D,E,F,GSQL> Select a,
2 Substr(b || ',', Instr(b, ',', 1, b.Rid) + 1,
3 Instr(',' || b || ',',
',', 1, b.Rid + 1) - 1 - Instr(',' || b || ',', ',', 1, b.Rid)) b
4 From t,
5 (Select Rownum Rid
6 From Dual
7 Connect By Rownum <= (Select Length(Replace(Translate(b
, Replace(b, ',', ''), ' '), ' ', '')) From t) + 1) b;A B
---------- ------------------------------
A C
A D
A E
A F
A G
A B已选择6行。
假设b最多由100个值拼接
下面是拆分语句
select a,substr(b,instr(','||b,',',1,rn),instr(','||b||',',',',1,rn+1)-instr(','||b||',',',',1,rn)-1),rn from c,(select rownum rn from dual connect by rownum<100) where instr(b||',',',',1,rn)>0 order by a,rn
这里的100是一个常量,如果拼接个数较多,可以用更大的数代替。