Oracle 字符串分割

来源：互联网发布：淘宝买药付款流程编辑：程序博客网时间：2024/06/06 20:34

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) splitedFROM DUALCONNECT BY ROWNUM <=LENGTH ('first field, second field , third field') - LENGTH (REPLACE ('first field, second field , third field', ',', '')) +1;SPLITED--------------------first fieldsecond fieldthird 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                       2aa,bb,                                         2                       5bb,                                            3                       8

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

0 0