Oracle使用REGEXP_SUBSTR匹配可选BC分组提取目标字符串问题

需求说明 #

在Oracle环境中使用REGEXP_SUBSTR实现字符串提取，匹配规则如下：

• 字符串末尾为STU/STUDENT时，返回中间XXBC字段（无BC字段时则为末尾STU/STUDENT字段）之前的姓名前缀
• 字符串末尾为TEACHER时返回NULL

预期匹配效果 #

Test 1.Input: JOHN 10BC STUDENT      Desired Output: JOHN
Test 2.Input: JOHN  STUDENT          Desired Output: JOHN
Test 3.Input: JOHN 10BC STU          Desired Output: JOHN
Test 4.Input: JOHN 10BC TEACHER      Desired Output: NULL
Test 5.Input: JOHN  TEACHER          Desired Output: NULL
Test 6.Input: MR JOHN 08BC STU       Desired Output: MR JOHN
Test 7.Input: MR JOHN STUDENT        Desired Output: MR JOHN
Test 8.Input: MR JOHN 07BC TEACHER   Desired Output: NULL
Test 9.Input: MR STUART 06BC STUDENT Desired Output: MR STUART
Test 10.Input: MR STUART LEE 05BC STUDENT Desired Output: MR STUART LEE

已尝试写法及问题 #

第一版写法（BC分组加可选标识?） #

测试1（有BC字段）执行语句：

select REGEXP_SUBSTR('JOHN 10BC STUDENT','(.*)(\s+.*BC)?\sSTU(DENT)?',1,1,'i',1) from dual; 

返回结果为JOHN 10BC，不符合预期的JOHN。

测试2（无BC字段）执行同规则语句：

select REGEXP_SUBSTR('JOHN STUDENT','(.*)(\s+.*BC)?\sSTU(DENT)?',1,1,'i',1) from dual; 

返回JOHN，符合预期。

第二版写法（移除BC分组的可选标识?） #

测试1（有BC字段）执行语句：

select REGEXP_SUBSTR('JOHN 10BC STUDENT','(.*)(\s+.*BC)\sSTU(DENT)?',1,1,'i',1) from dual; 

返回JOHN，符合预期。

测试2（无BC字段）执行同规则语句：

select REGEXP_SUBSTR('JOHN STUDENT','(.*)(\s+.*BC)\sSTU(DENT)?',1,1,'i',1) from dual; 

返回NULL，不符合预期的JOHN。

问题根因 #

第一版写法出错是因为Oracle正则默认采用贪婪匹配模式，(.*)会尽可能匹配最长字符，当(\s+.*BC)?设为可选时，正则引擎会优先把 10BC匹配进第一个捕获组，跳过可选的BC分组，导致返回结果多携带BC字段内容。
第二版写法强制要求BC分组必须存在，因此无BC字段的场景直接匹配失败返回NULL，无法兼容无BC的输入场景。

正确实现写法 #

采用非贪婪匹配+结尾锚定的规则，避免.*过度匹配，同时兼容BC字段可选、仅末尾为STU/STUDENT才匹配的要求：

SELECT REGEXP_SUBSTR(
  待匹配字段/字符串,
  '^(.*?)(\s+\S*BC)?\s+STU(DENT)?$',
  1,1,'i',1
) FROM dual;

规则说明 #

• ^ 匹配字符串开头，避免局部片段误匹配
• (.*?) 非贪婪匹配姓名前缀，尽可能少匹配字符，为后续规则预留匹配空间
• (\s+\S*BC)? 可选BC字段组：匹配1个以上空白符+任意非空白字符+BC结尾的内容，对应 10BC这类片段
• \s+STU(DENT)?$ 匹配结尾部分：1个以上空白符+STU开头，后面可选拼接DENT，直到字符串结尾，仅末尾是STU/STUDENT时匹配成功，末尾为TEACHER时匹配失败返回NULL
• 最后一个参数1表示返回第一个捕获组（即姓名前缀）的内容，'i'参数表示大小写不敏感匹配。

该写法可覆盖所有列出的测试场景，返回符合预期的结果。

内容的提问来源于stack exchange，提问作者kishore