Question

oracle中如何将某个字段中内容带有分隔符字

Answer 1

with temp as
(
select name as text from a
)
select substr(text,instr(text,',',1,rn)+1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1) text from
(
select ','||t1.text||',' text,t2.rn from
(select text,length(text)-length(replace(text,',',''))+1 rn from temp) t1,
(select rownum rn from all_objects where rownum <= (select max(length(text)-length(replace(text,',',''))+1) rn from temp)) t2
where t1.rn >= t2.rn order by text,rn
)

用这种方法可以处理name这一列，如果你想ID、NAME都先显示的话，就要写成一个函数来调用了。