oracle substr小实例

问题：

有如下字符串
,aa,,cc,dd
我需要弄成
1 aa
2 null
3 cc
4 dd

 

解法：

SQL> SELECT id,
  2         NVL(SUBSTR(
  3                 col1,
  4                 INSTR(col1,',',1,id)+1,
  5                 INSTR(col1,',',1,id+1)-INSTR(col1,',',1,id)-1
  6               ),'null') AS res
  7  FROM
  8  (
  9    WITH t AS
 10    (
 11      SELECT ',aa,,cc,dd'||',' as col1
 12      FROM dual
 13    )
 14    SELECT col1,
 15           LEVEL id
 16    FROM t
 17    CONNECT BY LEVEL<=LENGTH(col1)-
 18                      LENGTH(REPLACE(col1,','))
 19                      -1
 20  );

        ID RES
---------- ----------------------
         1 aa
         2 null
         3 cc
         4 dd

原帖：http://www.itpub.net/thread-1407258-1-1.html