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
         2null
         3 cc
         4 dd

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