如何使用SQL的REGEXP_SUBSTR从文本字段中分离提取目标数字

核心逻辑是先过滤掉文本里两类日期格式的数字，再提取剩余的独立数字，最后按顺序拼接成要求的格式。 #

第一步：明确识别规则 #

• 需要过滤的日期格式共2种：
  • 点分隔长日期：格式为dd.mm.yyyy，例如06.07.2022
  • 斜杠分隔短日期：格式为dd/mm，例如13/07、11/07
• 需要提取的目标业务数字：不属于上述日期结构的独立数值，也就是数字前后不会跟日期分隔符（.、/）或其他连续数字。

第二步：SQL实现代码 #

写法1：支持正则预查的数据库（Oracle、MySQL 8.0+等） #

直接通过正则规则跳过日期内的数字，按顺序提取第1、第2个匹配到的业务值拼接即可：

SELECT 
  CONCAT(
    REGEXP_SUBSTR(comment_text, '(?<![./\d])\d+(?![./\d])', 1, 1),
    ' and ',
    REGEXP_SUBSTR(comment_text, '(?<![./\d])\d+(?![./\d])', 1, 2),
    ', '
  ) AS biz_value
FROM 你的员工反馈表名;

正则(?<![./\d])\d+(?![./\d])的含义：匹配1位及以上的数字，要求数字前一位不能是.、/或其他数字，数字后一位也不能是.、/或其他数字，自动过滤掉嵌在日期里的数字。

写法2：低版本兼容写法（不支持正则预查的场景） #

先通过两次REGEXP_REPLACE把两类日期整体替换为空，再从剩余文本里提取数字，所有支持REGEXP基础函数的数据库都能运行：

SELECT 
  CONCAT(
    REGEXP_SUBSTR(
      REGEXP_REPLACE(
        REGEXP_REPLACE(comment_text, '\\d{2}\\.\\d{2}\\.\\d{4}', ''),
        '\\d{2}/\\d{2}', ''
      ),
      '\\d+', 1, 1
    ),
    ' and ',
    REGEXP_SUBSTR(
      REGEXP_REPLACE(
        REGEXP_REPLACE(comment_text, '\\d{2}\\.\\d{2}\\.\\d{4}', ''),
        '\\d{2}/\\d{2}', ''
      ),
      '\\d+', 1, 2
    ),
    ', '
  ) AS biz_value
FROM 你的员工反馈表名;

结果验证 #

针对给出的3条示例文本，两种写法的返回结果完全匹配预期：

7 and 5, 
8 and 13, 
7 and 12, 

如果业务数值包含小数，只需要把正则里匹配整数的\d+改成\d+(\.\d+)?即可，不会和日期里的点分隔符产生冲突。

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