Oracle中REGEXP_SUBSTR函数的使用

一、基本介绍

REGEXP_SUBSTR函数是一个强大的字符串处理函数，它允许你使用正则表达式（Regular Expressions）来搜索和提取字符串中的子字符串。这个函数在数据清洗、数据转换和复杂字符串操作中非常有用。

二、参数的解释说明

REGEXP_SUBSTR(source_char, pattern, position, occurrence, match_param)

source_char
必需的， 要搜索的源字符串。
pattern
必需的，用于匹配的正则表达式。
position
可选的。它是一个整数，指示开始搜索的起始位置。默认为 1。
occurrence
可选的。它是一个整数，指示要返回的是第几次出现。默认为 1。
match_param
可选的。执行匹配采用的模式：

• ‘i’ 指定不区分大小写的匹配，即使确定的条件排序规则区分大小写。
• ‘c’ 指定区分大小写和区分重音的匹配，即使确定的条件排序规则不区分大小写或不区分重音。
• …

三、基本用法

• 找出字符串 ‘12AB34cd45ef’ 中的连续的小写字母序列。

SELECT
    REGEXP_SUBSTR('12AB34cd45ef', '[a-z]+') Result
FROM dual;

• 如果源字符串中没有与给定的正则表达式匹配的内容， REGEXP_SUBSTR() 将返回 NULL。

SELECT
    REGEXP_SUBSTR('12AB34cd45ef', '[a-z]{3,}') Result
FROM dual;

• 找出字符串 ‘12AB34cd45ef’ 中的连续的小写字母序列，从第 9 个字符开始搜索

SELECT
    REGEXP_SUBSTR('12AB34cd45ef', '[a-z]+', 9) Result
FROM dual;

• 用来找出字符串 ‘12AB34cd45ef’ 中的第 2 次出现的连续的小写字母序列

SELECT
    REGEXP_SUBSTR('12AB34cd45ef', '[a-z]+', 1, 2) Result
FROM dual;

• 用来找出字符串 ‘12AB34cd45ef’ 中连续的字母序列，不区分大小写。

SELECT
    REGEXP_SUBSTR('12AB34cd45ef', '[a-z]+', 1, 1, 'i') Result
FROM dual;

四、和connect by函数结合使用造成的生产问题

1、和connect by 函数的简单使用

SELECT REGEXP_SUBSTR('12AB34cd45ef', '[a-z]+', 1, level, 'i') Result
  FROM dual
connect by level <= REGEXP_COUNT('12AB34cd45ef', '[a-z]+', 1, 'i')

CONNECT BY是Oracle SQL中的一个子句，用于定义层次结构或递归关系，从而进行层次结构数据的查询。
LEVEL是Oracle SQL中的一个伪列，用于在层次结构或递归查询中获取当前行的级别。
REGEXP_COUNT 用于计算字符串中正则表达式匹配的次数。

2、未使用PRIOR连接条件，引发生产数据重复

数据案例

错误的执行脚本

select t.id, regexp_substr(t.param_value, '[^,]+', 1, level) result
  from test_01 t
connect by level <= regexp_count(t.param_value, '[^,]+', 1)

正确的执行脚本

select t.id, regexp_substr(t.param_value, '[^,]+', 1, level) result
  from test_01 t
connect by level <= regexp_count(t.param_value, '[^,]+', 1)
       and prior id = id
       and prior SYS_GUID() is not null

原因分析

在 CONNECT BY 子句中，如果没有适当的 PRIOR 条件，每个记录的每个层级都会与其他记录的每个层级组合，导致大量的重复数据。

• and prior id = id 确保递归关系只在同一个 id 内部进行。
• and prior SYS_GUID() is not null 确保每次递归步骤都有一个唯一的标识符，防止无限循环。

上面只是本人浅显的理解和百度的结果，如果有大佬看到不满意的地方，请不吝赐教。