SQL实现分解带分隔符的字符串实验

SQL> var v_str varchar2(100);
SQL> exec :v_str:=',id1,id11,id101,';
PL/SQL procedure successfully completed.
SQL> select :v_str a,replace(:v_str,',','') b
  2    ,substr(:v_str,instr(:v_str,',',1,rownum)+1,
  3    instr(:v_str,',',1,rownum+1)-instr(:v_str,',',1,rownum)-1) c
  4  from dual
  5  connect by rownum<length(:v_str)-length(replace(:v_str,',',''));
A                                B                                C
-------------------------------- -------------------------------- --------------------------------
,id1,id11,id101,                 id1id11id101                     id1
,id1,id11,id101,                 id1id11id101                     id11
,id1,id11,id101,                 id1id11id101                     id101