如何在SQL查询中获取字符串模板与提取特定编号？

一、提取字符串中的特定编号 #

你给出的字符串里的编号是数字.数字.数字.数字这种格式，不同SQL数据库可以用正则匹配来提取，我给你分几种常用数据库的具体写法：

MySQL/MariaDB（8.0+） #

MySQL 8.0及以上支持REGEXP_SUBSTR函数，能直接匹配提取。如果要抓所有符合的编号，得结合临时数字表来处理多个匹配结果：

-- 提取第一个匹配的编号
SELECT REGEXP_SUBSTR('Questions that may already have your answer 107.755.741332.1 Questions that may already have your answer 117.700.741372.1', '[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+') AS first_id;

-- 提取所有匹配的编号（这里用临时数字表生成1-3的数字，覆盖可能的匹配数）
WITH nums AS (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3)
SELECT REGEXP_SUBSTR(your_column, '[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+', 1, n) AS id
FROM your_table, nums
WHERE REGEXP_SUBSTR(your_column, '[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+', 1, n) IS NOT NULL;

PostgreSQL #

PostgreSQL的regexp_matches函数可以直接返回所有匹配的结果，配合unnest把数组转成行：

-- 从指定字符串提取所有编号
SELECT unnest(regexp_matches('Questions that may already have your answer 107.755.741332.1 Questions that may already have your answer 117.700.741372.1', '[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+', 'g')) AS id;

-- 从表的列中提取
SELECT unnest(regexp_matches(your_column, '[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+', 'g')) AS id
FROM your_table;

SQL Server（2016+） #

SQL Server可以用循环提取或者OPENJSON配合正则，这里给两种方案：

-- 方案1：循环提取适合少量匹配的场景
DECLARE @str NVARCHAR(MAX) = 'Questions that may already have your answer 107.755.741332.1 Questions that may already have your answer 117.700.741372.1';
DECLARE @result TABLE (id NVARCHAR(50));

WHILE PATINDEX('%[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+%', @str) > 0
BEGIN
    INSERT INTO @result
    SELECT SUBSTRING(@str, PATINDEX('%[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+%', @str), CHARINDEX(' ', @str + ' ', PATINDEX('%[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+%', @str)) - PATINDEX('%[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+%', @str));
    SET @str = STUFF(@str, 1, CHARINDEX(' ', @str + ' ', PATINDEX('%[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+%', @str)), '');
END

SELECT * FROM @result;

-- 方案2：用OPENJSON更简洁
SELECT value AS id
FROM OPENJSON('["' + REPLACE(REGEXP_REPLACE(@str, '[^0-9.]', ' '), ' ', '","') + '"]')
WHERE value LIKE '[0-9]%.[0-9]%.[0-9]%.[0-9]%' AND value <> '';

二、获取两种字符串模板 #

如果你说的“获取两个字符串模板”是指从文本中提取符合两种特定格式的字符串，核心思路就是用正则表达式的逻辑或把两种模板的规则合并，然后用对应数据库的正则提取函数来抓结果。

举个实际例子：假设你要同时提取数字.数字.数字.数字和Q-[0-9]+（比如Q-123这种格式），以PostgreSQL为例可以这么写：

SELECT unnest(regexp_matches(your_column, '[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+|Q-[0-9]+', 'g')) AS matched_string
FROM your_table;

这里的|就是“或”的意思，把两个模板的正则规则放在两边，就能同时匹配两种格式的字符串了。不同数据库的正则语法略有差异，但核心逻辑都是一样的——先定义每种模板的匹配规则，再合并规则进行提取。

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