SQL Server从字符串提取多个EGG前缀ID生成逗号分隔列表

由于SQL Server原生支持PATINDEX的简单通配符匹配，我们可以通过递归CTE遍历所有合法ID的位置，提取后拼接即可满足需求，无需正则支持。

适用SQL Server 2017及以上版本（支持STRING_AGG） #

WITH MSSQL_SAMPLE_DATA (TEXT_EXAMPLE) AS
(
       SELECT 'Today is a EGG01234 EGG12345 fine day EGG67891'
       UNION ALL
       SELECT 'Blah Blah Blah'
       UNION ALL
       SELECT 'EGG01234, EGG12345 test test'
),
RecursiveEGGSearch AS (
    -- 锚点：查找每条文本第一个合法ID的起始位置
    SELECT 
        TEXT_EXAMPLE,
        PATINDEX('%EGG[0-9][0-9][0-9][0-9][0-9]%', TEXT_EXAMPLE) AS EggPos,
        CAST(NULL AS VARCHAR(8)) AS ValidId
    FROM MSSQL_SAMPLE_DATA
    UNION ALL
    -- 递归：从上一个ID结束位置继续查找下一个合法ID
    SELECT 
        r.TEXT_EXAMPLE,
        PATINDEX('%EGG[0-9][0-9][0-9][0-9][0-9]%', SUBSTRING(r.TEXT_EXAMPLE, r.EggPos + 8, LEN(r.TEXT_EXAMPLE))) + r.EggPos + 7 AS NextEggPos,
        CAST(SUBSTRING(r.TEXT_EXAMPLE, r.EggPos, 8) AS VARCHAR(8)) AS ValidId
    FROM RecursiveEGGSearch r
    WHERE r.EggPos > 0
)
SELECT 
    TEXT_EXAMPLE,
    CASE WHEN COUNT(ValidId) = 0 THEN NULL ELSE STRING_AGG(ValidId, ',') END AS OUTPUT
FROM RecursiveEGGSearch
GROUP BY TEXT_EXAMPLE
OPTION (MAXRECURSION 1000); -- 若单条文本ID数量超过100可调整该参数上限

兼容SQL Server 2016及更早版本（无STRING_AGG） #

使用FOR XML PATH实现字符串拼接：

WITH MSSQL_SAMPLE_DATA (TEXT_EXAMPLE) AS
(
       SELECT 'Today is a EGG01234 EGG12345 fine day EGG67891'
       UNION ALL
       SELECT 'Blah Blah Blah'
       UNION ALL
       SELECT 'EGG01234, EGG12345 test test'
),
RecursiveEGGSearch AS (
    SELECT 
        TEXT_EXAMPLE,
        PATINDEX('%EGG[0-9][0-9][0-9][0-9][0-9]%', TEXT_EXAMPLE) AS EggPos,
        CAST(NULL AS VARCHAR(8)) AS ValidId
    FROM MSSQL_SAMPLE_DATA
    UNION ALL
    SELECT 
        r.TEXT_EXAMPLE,
        PATINDEX('%EGG[0-9][0-9][0-9][0-9][0-9]%', SUBSTRING(r.TEXT_EXAMPLE, r.EggPos + 8, LEN(r.TEXT_EXAMPLE))) + r.EggPos + 7 AS NextEggPos,
        CAST(SUBSTRING(r.TEXT_EXAMPLE, r.EggPos, 8) AS VARCHAR(8)) AS ValidId
    FROM RecursiveEGGSearch r
    WHERE r.EggPos > 0
),
ValidIds AS (
    SELECT DISTINCT TEXT_EXAMPLE, ValidId 
    FROM RecursiveEGGSearch 
    WHERE ValidId IS NOT NULL
)
SELECT 
    t.TEXT_EXAMPLE,
    STUFF((
        SELECT ',' + v.ValidId 
        FROM ValidIds v 
        WHERE v.TEXT_EXAMPLE = t.TEXT_EXAMPLE 
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, NULL) AS OUTPUT
FROM MSSQL_SAMPLE_DATA t
OPTION (MAXRECURSION 1000);

逻辑说明 #

• 利用PATINDEX的通配符规则%EGG[0-9][0-9][0-9][0-9][0-9]%直接匹配EGG+5位数字的合法ID格式，自动过滤无效的EGG开头内容
• 递归CTE每次提取当前合法ID后，从ID结束位置（ID总长8位）继续查找下一个ID，直到遍历完整个文本
• 最终将同一条文本的所有合法ID拼接为逗号分隔的字符串，无ID的行返回NULL

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