oracle 分割字符串

drop table qc_str   
create table qc_str(aa number(10),bb varchar(64))
insert into qc_str values(1,'aa,bb,cc,dd');
insert into qc_str values(2,'c,d,e,f,g,h,i');

select a.aa,
       substr(bb,instr(a.bb,',',1,b.num-1)+1,instr(a.bb,',',1,b.num)-instr(a.bb,',',1,b.num-1)-1)
  from(select aa, ','||ltrim(rtrim(bb,','),',')||',' bb from qc_str ) a ,
      (select rownum+1 num from dual connect by rownum<=(select max(length(bb)-length(replace(bb,',',''))) num from qc_str)) b
where length(bb)-length(replace(bb,',',''))>=b.num
order by a.aa--1,2