第一种方法:
select substr(name,1,instr(name,',',1)-1) name from testc
union all
select substr(name,instr(name,',',1)+1,instr(name,',',1,2)-instr(name,',',1)-1) from testc
union all
select substr(name,instr(name,',',1,2)+1,length(name)-instr(name,',',1,2)) from testc
第二种方法:
select substr(','||t1.name||',',instr(','||t1.name | |',',',',1,rn)+1,instr(',' | |t1.name | |',',',',1,rn+1)-instr(',' | |t1.name | |',',',',1,rn)-1) as name
from testc t1,
(
select rownum rn
from all_objects
where rownum <= 10
)t2
where instr(',' | |t1.name | |',',',',1,rn+1) > 0