Oracle存在多匹配项时如何提取S9C加8位数字规则的子串

需求规则拆解 #

需要提取的子串需同时满足两个条件：

• 固定以S9C开头
• 开头后紧跟恰好8位阿拉伯数字，且8位数字后不能再跟其他数字

匹配逻辑说明 #

正则规则使用S9C[0-9]{8}(?!\d)即可覆盖要求：

• S9C匹配固定前缀
• [0-9]{8}匹配刚好8位阿拉伯数字
• (?!\d)为负向预查逻辑，确保匹配到的8位数字后没有其他数字，避免把前缀后接9位及以上数字的场景错误匹配

代码实现 #

场景1：返回最后一个匹配项（匹配示例输出） #

如果需要和示例输出一致，多个匹配项时取最后一个，直接用REGEXP_SUBSTR即可：

-- 假设你的表名为your_table，存储待匹配字符串的字段为content，唯一标识字段为id
SELECT
  id,
  REGEXP_SUBSTR(content, 'S9C[0-9]{8}(?!\d)', 1, -1) AS extract_result
FROM your_table;

参数说明：

• 第3个参数1：代表从字符串第1位开始匹配
• 第4个参数-1：代表从后往前数取第一个匹配项（即所有符合规则的匹配项里的最后一个），如果需要取第一个匹配项把-1改成1即可

场景2：返回所有匹配项 #

如果需要把同一个字符串里所有符合规则的匹配项全部返回，可使用分层查询拆分：

SELECT
  t.id,
  REGEXP_SUBSTR(t.content, 'S9C[0-9]{8}(?!\d)', 1, LEVEL) AS extract_result
FROM your_table t
CONNECT BY LEVEL <= REGEXP_COUNT(t.content, 'S9C[0-9]{8}(?!\d)')
  AND PRIOR t.id = t.id
  AND PRIOR SYS_GUID() IS NOT NULL;

示例验证 #

用给出的4条测试数据验证，返回结果完全匹配预期：

1. 第一条数据S9C后接9位数字，负向预查不通过，返回NULL
2. 第二条数据S9C后接字母，不满足8位数字要求，返回NULL
3. 第三条数据仅1个符合规则的匹配项，返回S9C00019171
4. 第四条数据有2个符合规则的匹配项，取最后一个返回S9C10204555

兼容低版本说明 #

如果你使用的是Oracle 11g以下版本，不支持负向预查和反向取数，可调整正则规则实现同等效果：

SELECT
  id,
  CASE WHEN REGEXP_LIKE(content, 'S9C[0-9]{8}([^0-9]|$)') 
       THEN SUBSTR(REGEXP_SUBSTR(content, 'S9C[0-9]{8}([^0-9]|$)', 1, -1), 1, 11)
       ELSE NULL END AS extract_result
FROM your_table;

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