使用正则表达式提取数据库12位整数遇问题，求正确方案

需要从数据库的description列中提取固定12位的整数字符串，该字符串随机出现在各类混合字符内容中，无匹配时返回null。

示例数据 #

期望结果 #

尝试过的错误语句及问题 #

• 用SUBSTR+INSTR的方式，无法过滤非12位的无效匹配（比如200DONE,NA）：

SELECT ID, description, SUBSTR(description, INSTR(description,' 200'), 13) AS extracted_numbers FROM inventory_table WHERE description LIKE '% 200%';

• 用\b单词边界的REGEXP_SUBSTR语句返回null，因为部分数据库（如Oracle）不支持\b作为单词边界：

SELECT
  ID,
  description,
  REGEXP_SUBSTR(description, '\b[0-9]{12}\b')
FROM inventory_table;

• 用/d匹配数字的语句无效，正则中匹配数字应使用\d，语法错误导致返回null：

SELECT
  ID,
  description,
  REGEXP_SUBSTR(description, '/d/d/d/d/d/d/d/d/d/d/d/d', 1, LEVEL) AS extracted_numbers
FROM inventory_table
CONNECT BY LEVEL <= REGEXP_COUNT(description, '/d/d/d/d/d/d/d/d/d/d/d/d');

正确实现方案 #

不同数据库的正则语法略有差异，以下是主流数据库的解决方案：

Oracle #

Oracle使用\W匹配非单词字符，结合捕获组提取目标内容：

SELECT
  ID,
  description,
  REGEXP_SUBSTR(description, '(^|\W)([0-9]{12})(\W|$)', 1, 1, NULL, 2) AS extracted_numbers
FROM inventory_table;

• 逻辑：确保12位数字的前后是字符串边界或非单词字符，避免匹配更长数字的一部分；最后一个参数2指定返回第二个捕获组（即12位数字本身）。

MySQL/MariaDB #

MySQL支持[[:<:]]和[[:>:]]作为单词边界（兼容所有版本），或MySQL 8.0+可用\b：

-- 兼容所有MySQL版本
SELECT
  ID,
  description,
  REGEXP_SUBSTR(description, '[[:<:]][0-9]{12}[[:>:]]') AS extracted_numbers
FROM inventory_table;

-- MySQL 8.0+ 可用
SELECT
  ID,
  description,
  REGEXP_SUBSTR(description, '\\b[0-9]{12}\\b') AS extracted_numbers
FROM inventory_table;

PostgreSQL #

PostgreSQL使用\m和\M作为单词边界，通过SUBSTRING提取：

SELECT
  ID,
  description,
  SUBSTRING(description FROM '\m\d{12}\M') AS extracted_numbers
FROM inventory_table;

以上方案均会在无匹配时返回null，且仅提取符合要求的12位连续数字。

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