如何在Oracle中用SUBSTRING或REGEXP_SUBSTR提取S9C开头带数字子串

实现方案 #

直接使用Oracle自带的正则函数组合即可实现需求，核心用到REGEXP_SUBSTR和REGEXP_COUNT两个函数：

核心逻辑说明

• 匹配规则：用正则S9C\d+匹配S9C开头且后续紧跟至少1个数字的子串，自动过滤S9C后接非数字的无效场景（比如示例2中的S9CB就不会被命中）
• 取最后一个匹配项：先用REGEXP_COUNT统计文本中符合规则的子串总个数，再将总个数作为REGEXP_SUBSTR的匹配次数参数传入，即可直接返回最后一个匹配结果；如果没有匹配项，REGEXP_COUNT返回0，REGEXP_SUBSTR会自动返回Null，刚好符合要求。

完整测试SQL

WITH test_data AS (
  -- 示例输入数据
  SELECT 'CABLE : THERMINAL 3X2X0.25MM FPCP PLUS UNITRONIC S9C000019651' AS content FROM DUAL UNION ALL
  SELECT 'Motor Protection Relay EMR-3MPO-2S9CB1-1 (was IQ1000II / MP 3000)' AS content FROM DUAL UNION ALL
  SELECT 'GREASE : BEM 41-132 3KG CARTRIDGE KLUBERPLEX S9C00019171 (Order by KG''s required)' AS content FROM DUAL UNION ALL
  SELECT 'DO NOT USE CARRIER SPIDEX ZK 38 98 SH. S9C00011593 (SUPERSEDE BY S9C10204555 - WIN0020775)' AS content FROM DUAL
)
SELECT 
  REGEXP_SUBSTR(content, 'S9C\d+', 1, REGEXP_COUNT(content, 'S9C\d+')) AS result
FROM test_data;

运行后输出完全符合期望：

S9C000019651
Null
S9C00019171
S9C10204555

扩展适配

如果需要兼容S9C的大小写变体（比如s9c、S9c等），可以给正则函数添加忽略大小写的参数'i'，修改为：

REGEXP_SUBSTR(content, 'S9C\d+', 1, REGEXP_COUNT(content, 'S9C\d+', 1, 'i'), 'i')

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