--10g以上的话使用正则表达式 SQL> with t as( 2 select 'a,b,c;d,e,f;g,h,i;' txt from dual) 3 select replace(regexp_substr(txt,'[^;]+',1,level),',',' ') txt 4 from t 5 connect by 6 level<=length(txt)-length(replace(txt,';',''))+1 7 /
TXT ------------------------------------ a b c d e f g h i
--来个9i-10g都可用的 WITH t AS (SELECT 'a,b,c;d,e,f;g,h,i;' s FROM dual) SELECT substr(s, instr(s, ';', 1, LEVEL) + 1, instr(s, ';', 1, LEVEL + 1) - instr(s, ';', 1, LEVEL) - 1) FROM (SELECT ';' || s s FROM t) CONNECT BY LEVEL < length(s) - length(REPLACE(s, ';'));
--10g以上的话使用正则表达式
SQL> with t as(
2 select 'a,b,c;d,e,f;g,h,i;' txt from dual)
3 select replace(regexp_substr(txt,'[^;]+',1,level),',',' ') txt
4 from t
5 connect by
6 level<=length(txt)-length(replace(txt,';',''))+1
7 /
TXT
------------------------------------
a b c
d e f
g h i
WITH t AS
(SELECT 'a,b,c;d,e,f;g,h,i;' s FROM dual)
SELECT substr(s,
instr(s, ';', 1, LEVEL) + 1,
instr(s, ';', 1, LEVEL + 1) - instr(s, ';', 1, LEVEL) - 1)
FROM (SELECT ';' || s s FROM t)
CONNECT BY LEVEL < length(s) - length(REPLACE(s, ';'));