PostgreSQL中如何获取字符串内所有子串匹配项并提取数字？

针对你提供的ID字符串，要提取其中所有数字并每行显示一个，可通过以下两种PostgreSQL内置函数实现：

方法一：使用regexp_matches + unnest #

regexp_matches能全局匹配字符串中符合正则的子串，返回数组；unnest将数组展开为多行。

执行以下SQL语句（单字符串直接替换内容，表字段则替换为对应表名和字段名）：

-- 处理单字符串
SELECT unnest(regexp_matches('BS Score xxxxxxx075SCxxxBS Score xxxxxxx062SCxxxBS Score xxxxxxx115SCxxx', '\d+', 'g')) AS extracted_numbers;

-- 处理表中字段
SELECT unnest(regexp_matches(your_column_name, '\d+', 'g')) AS extracted_numbers
FROM your_table_name;

• \d+：匹配1个或多个连续数字
• 'g'：开启全局匹配，返回所有符合条件的子串

方法二：使用regexp_split_to_table #

通过非数字字符拆分字符串，再过滤空值得到数字子串：

-- 处理单字符串
SELECT regexp_split_to_table('BS Score xxxxxxx075SCxxxBS Score xxxxxxx062SCxxxBS Score xxxxxxx115SCxxx', '[^\d]+') AS extracted_numbers
WHERE regexp_split_to_table('BS Score xxxxxxx075SCxxxBS Score xxxxxxx062SCxxxBS Score xxxxxxx115SCxxx', '[^\d]+') <> '';

-- 处理表中字段
SELECT regexp_split_to_table(your_column_name, '[^\d]+') AS extracted_numbers
FROM your_table_name
WHERE regexp_split_to_table(your_column_name, '[^\d]+') <> '';

• [^\d]+：匹配1个或多个非数字字符，以此作为拆分分隔符
• WHERE子句：过滤拆分后产生的空字符串

以上两种方法都能得到预期的每行一个数字的结果。

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