个人工作记录,将会持续更新
1. REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
- 函数说明
string:需要进行正则处理的字符串
pattern:进行匹配的正则表达式
position:起始位置,从字符串的第几个字符开始正则表达式匹配(默认为1)
注意:字符串最初的位置是1而不是0
occurrence:获取第几个分割出来的组(分割后最初的字符串会按分割的顺序排列成组)
modifier:模式(‘i’不区分大小写进行检索;‘c’区分大小写进行检索。默认为’c’)针对的是正则表达式里字符大小写的匹配 - 补充说明
connect by Level
REGEXP_SUBSTR函数一般和connect by Level配合使用,Level的值决定了生成的行数(子串) - 例子
select distinct * from (
select regexp_substr(q.nums, ‘[^,]+’, 1, Level,‘i’) order_num
from (
select ‘A,B,C,D,E’ nums from dual
) q
connect by Level <= LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ‘,’, ‘’)) + 1) order by order_num;
- 结果展示
select ‘A,B,C,D,E’ nums from dual
select distinct * from (
select regexp_substr(q.nums, ‘[^,]+’, 1, Level,‘i’) order_num
from (
select ‘A,B,C,D,E’ nums from dual
) q
connect by Level <= LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ‘,’, ‘’)) + 1) order by order_num;