把字符串 '1,2,3,4,5'，以逗号分隔，输出为行，也就是

1
2
3
4
5

随手写了一个, Oracle 10G 以上
有表如下

 
  
  1. SQL> select * from t; 
  2.         ID NAME 
  3. ---------- ---------------- 
  4.          1 0,1,5,2,8,10 
  5.          2 9,7,8 
  6.          3 你好,他好,大家好 
 
  
 
  
 
  
  1. with vmaxnum as ( 
  2.      select  rownum ele 
  3.      from    dual 
  4.      connect by rownum<=(select max(length(name)-length(replace(name,',',null)))+1 from t)) 
  5. select id, 
  6.        decode(pos,0,substr(name,lagpos+1),substr(name,lagpos+1,pos-lagpos-1)) name 
  7. from   (select id,name,ele,pos,nvl(lag(pos) over (partition by id order by ele),0) lagpos 
  8.         from   (select id,name,ele, 
  9.                        instr(name,',',1,ele) pos 
  10.                 from   (select /*+ all_rows no_merge(v2) use_merge(t,v2) */ 
  11.                                t.id,t.name,v2.ele 
  12.                         from   t,vmaxnum v2 
  13.                         where  ele<=length(name)-length(replace(name,',',null))+1))) 
  14. order  by id,ele; 
 
  

         ID NAME
 ---------- --------
          1 0
          1 1
          1 5
          1 2
          1 8
          1 10
          2 9
          2 7
          2 8
          3 你好
          3 他好
          3 大家好
 
  
12 rows selected.
 
  
然后客户那反馈说数据库说9i的，报错说connect by不能使用子查询。
 于是再稍做修改，在确定逗号不会超过99个的前提下，直接写个常量100，可以支持9i
  
 
  
 
  
  1. with   vmaxnum as ( 
  2.        select  rownum ele 
  3.        from    dual 
  4.        connect by rownum<=100) 
  5. select id, 
  6.        decode(pos,0,substr(name,lagpos+1),substr(name,lagpos+1,pos-lagpos-1)) name 
  7. from   (select id,name,ele,pos,nvl(lag(pos) over (partition by id order by ele),0) lagpos 
  8.         from   (select id,name,ele, 
  9.                        instr(name,',',1,ele) pos 
  10.                 from   (select /*+ all_rows no_merge(v2) use_merge(t,v2) */ 
  11.                                t.*,v2.ele 
  12.                         from   t,vmaxnum v2 
  13.                         where  ele<=length(name)-length(replace(name,',',null))+1))) 
  14. order  by id,ele; 
 
  
后来想想这样也不好，于是建议建立一张特定的IOT表，保存10000个数字，一般够用了。
 建立一张IOT,共10000行，同样支持9i
 
  
 
  
  1. create table tnumber(ele,constraint pk_tnumber primary key(ele)) organization index as 
  2. select rownum id from dual connect by rownum<=10000; 
 
  
然后不再需要常量100，即保证准确，又保证较好的性能
 
  
 
  
  1. create table t500 nologging as 
  2. with   vmaxnum as ( 
  3.        select  ele 
  4.        from    tnumber 
  5.        where   ele<=(select max(length(name)-length(replace(name,',',null)))+1 from t)) 
  6. select id, 
  7.        decode(pos,0,substr(name,lagpos+1),substr(name,lagpos+1,pos-lagpos-1)) name 
  8. from   (select id,name,ele,pos,nvl(lag(pos) over (partition by id order by ele),0) lagpos 
  9.         from   (select id,name,ele, 
  10.                        instr(name,',',1,ele) pos 
  11.                 from   (select /*+ all_rows no_merge(v2) use_merge(t,v2) */ 
  12.                                t.id,t.name,v2.ele 
  13.                         from   t,vmaxnum v2 
  14.                         where  ele<=length(name)-length(replace(name,',',null))+1))) 
  15. order  by id,ele; 
 
  
        ID NAME
 ---------- --------
          1 0
          1 1
          1 5
          1 2
          1 8
          1 10
          2 9
          2 7
          2 8
          3 你好
          3 他好
          3 大家好
 
  
12 rows selected.
 
  
下面测试一下性能：转换50万行试试，测试环境：DELL D630 用了4年半的旧笔记本，磁盘都是碎片
 SQL> insert into t select rownum+4,'1,2,3,4' from dual connect by rownum<=500000;
 
  
500000 rows created.
 
  
SQL> commit;
 
  
Commit complete.
 
  
set timi on
 SQL> set timi on
 SQL> create table t500 nologging as
      with   vmaxnum as (
             select  ele
             from    tnumber
             where   ele<=(select max(length(name)-length(replace(name,',',null)))+1 from t))
      select id,
             decode(pos,0,substr(name,lagpos+1),substr(name,lagpos+1,pos-lagpos-1)) name
      from   (select id,name,ele,pos,nvl(lag(pos) over (partition by id order by ele),0) lagpos
              from   (select id,name,ele,
                             instr(name,',',1,ele) pos
                      from   (select /*+ all_rows no_merge(v2) use_merge(t,v2) */
                                     t.id,t.name,v2.ele
                              from   t,vmaxnum v2
                              where  ele<=length(name)-length(replace(name,',',null))+1)))
      order  by id,ele;
 
  
Table created.
 
  
Elapsed: 00:00:16.48
 
  
耗时16秒，含建表的写盘时间
 
  
SQL> select count(*) from t500;
 
  
  COUNT(*)
 ----------
    2000012
 
  
当然还有别的写法，比如简单的可以这样
 
  
 
  
  1. select  id,substr(name,instr(name,',',1,rownum)+1,instr(name,',',1,rownum+1)-instr(name,',',1,rownum)-1) name 
  2. from    (select  id,','||name||',' name from t) 
  3. connect by rownum<length(translate(name,','||name,',')); 
 
  
或者10G以上用正则表达式也可以实现
 这个方法可能不是最高效的，但也还可以。
 这个SQL的性能和字符串含有的逗号的个数有关，逗号越多，也就是分隔项越多，性能越差。