PostgreSQL 如何通过模式匹配提取两类特定格式的数字序列

你之前使用的^[0-9]+$正则规则仅能匹配完全由数字组成的整段字段内容，且没有限制长度和格式规则，因此无法提取嵌入在文本中的目标编号。

匹配逻辑说明 #

需要同时覆盖两种目标编号规则，且要避免从更长的无关数字序列中截断出符合长度的片段，推荐使用负向前后断言限定匹配内容的前后无其他数字：

• 匹配分支1：\d{4}-\d{7} 对应4位数字+横杠+7位数字的格式
• 匹配分支2：\d{11} 对应无横杠的11位连续数字格式
• 前后加(?<!\d)和(?!\d)负向断言，确保匹配内容前后没有其他数字，避免误匹配长数字片段

实现语句 #

单条匹配（每个字段仅1个目标编号） #

如果每个字段中仅存在1个目标编号，直接使用SUBSTRING函数即可：

SELECT
   SUBSTRING("my_field", '(?<!\d)(?:\d{4}-\d{7}|\d{11})(?!\d)' ) AS target_number
FROM 
    my_table;

全局匹配（每个字段可能有多个目标编号） #

如果单个字段中可能存在多个符合要求的编号，使用regexp_matches函数加全局匹配参数g，会以数组形式返回所有匹配结果：

SELECT
   regexp_matches("my_field", '(?<!\d)(?:\d{4}-\d{7}|\d{11})(?!\d)', 'g' ) AS target_numbers
FROM 
    my_table;

效果验证 #

上述规则可以准确匹配你给出的示例：

• 文本Project number 12345678912345 with id 2020-2583697 1456 提取结果为2020-2583697
• 文本Project 12345678912345 sequence 20202583697 1456 提取结果为20202583697

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