比如,有如下以逗号分隔的字符串:
aaa,bbbb,cccc,dd,eeee
要将其转化为多列:
aaa
bbbb
cccc
dd
eeee
解决方法:
SELECT REGEXP_SUBSTR('aaa,bbbb,cccc,dd,eeee', '[^,]+', 1, rownum)
from dual
connect by level <= LENGTH(regexp_replace('aaa,bbbb,cccc,dd,eeee', '[^,]+', ''));
主要用到3个技巧:
1.求出字符串中分隔符出现的次数
常用方法:
SELECT LENGTH(REGEXP_REPLACE(REPLACE('1234565', '5', '@'), '[^@]+', '')) COUNT FROM DUAL;
SELECT LENGTHB(TRANSLATE('ABCDEFGEFGDBE','E'||'ABCDEFGEFGDBE','E')) FROM DUAL;
SELECT LENGTHB('ABCDEFGEFGDBE')-LENGTHB(REPLACE('ABCDEFGEFGDBE','E','')) FROM DUAL;
2. 使用regexp_substr函数截取子字符串
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr :需要进行正则处理的字符串
__pattern :进行匹配的正则表达式
__position :起始位置,从第几个字符开始正则表达式匹配(默认为1)
__occurrence :标识第几个匹配组,默认为1
__modifier :模式('i'不区分大小写进行检索;'c'区分大小写进行检索。默认为'c'
3.使用connect by level生成多行
比如:
select level from dual connect by level <= 5
会得到:
1
2
3
4
5
参考文档
REGEXP_SUBSTR函数的整理
http://blog.itpub.net/29320885/viewspace-1776036/
Oracle的connect by level的使用
https://www.cnblogs.com/jason2018524/p/10288258.html