Oracle 字符串分割

Java的String有Split()方法，Oracle SQL也可以实现同样功能。该功能是Listagg()的逆运算。

1. 使用正则表达式

$hr@ORCL> col splited format a20
$hr@ORCL> SELECT REGEXP_SUBSTR('first field,second field,third field', '[^,]+', 1,rownum) splited
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH ('first field, second field , third field') - LENGTH (REPLACE ('first field, second field , third field', ',', '')) +1;

SPLITED
--------------------
first field
second field
third field

2. 使用substr, instr函数组合

select substr(t,1,instr(t,',',1)-1) splited from (
  SELECT substr(s,instr(s,',',1,ROWNUM)+1)||',' AS t,ROWNUM AS d ,instr(s,',',1,ROWNUM)+1 FROM (
    SELECT ',tt,aa,bb' AS s FROM dual
  )
CONNECT BY instr(s,',','1',ROWNUM)>1);

SPLITED            
--------------------
tt                   
aa                   
bb 

假如connect by 没有prior，会产生无穷多行。比如：

select * from dual connect by rownum >= 1;

$hr@ORCL>   SELECT substr(s,instr(s,',',1,ROWNUM)+1)||',' AS t,ROWNUM AS d ,instr(s,',',1,ROWNUM)+1 FROM (
    SELECT ',tt,aa,bb' AS s FROM dual
  )
CONNECT BY instr(s,',','1',ROWNUM)>1;

T                                              D INSTR(S,',',1,ROWNUM)+1
------------------------------------- ---------- -----------------------
tt,aa,bb,                                      1                       2
aa,bb,                                         2                       5
bb,                                            3                       8

这个SQL中由于connect by 会产生无穷多行，意即rownum无限增大，但是rownum作为instr的occurrence参数时，3以后会返回0. 再给定条件>1，从而将结果集巧妙的限定为s中逗号的出现次数。