SQL Server中为xx-xxxx模式字符串批量前置追加‘Product No’

问题背景 #

现有一张包含ID和Comments列的表，Comments列示例内容如下：

55-9988, Version 1.0 dated 07/20/2009
3684 for 66-0022
IB from Microsoft , for Monovalent A/king Influenza Subvirion Vaccine, Version 1.0 dated 06/27/2009
Package Insert from Microsoft , for Fluzone, dated 06/2008
Package Insert from Microsoft , for H5N1, dated 04/2007
IB from google, for AS93 as an Adjuvant for use with a king Vaccine, Version 1.0 dated 07/2009
Package Insert from Microsoft , for Fluzone, Version 37 dated 06/18/2008
55-9988 MIA, Version 1.0 dated 07/20/2009

需求是在每一处xx-xxxx格式的字符串（如66-0022）前方追加Product No （注意末尾空格），最终输出完整的修改后Comments内容。

方案1：适配SQL Server 2017及以上版本 #

利用STRING_SPLIT拆分字符串，对匹配格式的token进行替换，再通过STRING_AGG拼接回完整内容：

DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, tokens VARCHAR(1024));
INSERT INTO @tbl (tokens) VALUES
('55-9988, Version 1.0 dated 07/20/2009
3684 for 66-0022
IB from Microsoft , for Monovalent A/king Influenza Subvirion Vaccine, Version 1.0 dated 06/27/2009
Package Insert from Microsoft , for Fluzone, dated 06/2008
Package Insert from Microsoft , for H5N1, dated 04/2007
IB from google, for AS93 as an Adjuvant for use with a  king Vaccine, Version 1.0 dated 07/2009
Package Insert from Microsoft , for Fluzone, Version 37 dated 06/18/2008
55-9988 MIA, Version 1.0 dated 07/20/2009'),
('fafa');

DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10);

-- 拆分替换后重新拼接
SELECT 
    id,
    STRING_AGG(
        CASE 
            WHEN TRIM(',' FROM s.value) LIKE '[0-9][0-9]-[0-9][0-9][0-9][0-9]%' 
            THEN 'Product No ' + s.value
            ELSE s.value
        END, 
        ' '
    ) WITHIN GROUP (ORDER BY (SELECT 0)) AS modified_comments
FROM @tbl
CROSS APPLY STRING_SPLIT(REPLACE(tokens, @CrLf, ' '), ' ') s
GROUP BY id;

说明 #

1. 先将换行符替换为空格，统一拆分分隔符
2. 对每个拆分出的token判断是否匹配xx-xxxx格式
3. 匹配的token前追加Product No ，不匹配的保持原样
4. 用STRING_AGG将所有token拼接回完整字符串

方案2：适配SQL Server 2008及以上版本 #

通过XML拆分字符串，替换目标格式后再拼接回原内容：

DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, tokens VARCHAR(1024));
INSERT INTO @tbl (tokens) VALUES
('55-9988, Version 1.0 dated 07/20/2009
3684 for 66-0022
IB from Microsoft , for Monovalent A/king Influenza Subvirion Vaccine, Version 1.0 dated 06/27/2009
Package Insert from Microsoft , for Fluzone, dated 06/2008
Package Insert from Microsoft , for H5N1, dated 04/2007
IB from google, for AS93 as an Adjuvant for use with a  king Vaccine, Version 1.0 dated 07/2009
Package Insert from Microsoft , for Fluzone, Version 37 dated 06/18/2008
55-9988 MIA, Version 1.0 dated 07/20/2009'),
('fafa');

DECLARE @separator CHAR(1) = ' ';

WITH split_data AS (
    SELECT 
        id,
        x.value('.', 'VARCHAR(50)') AS token
    FROM @tbl
    CROSS APPLY (
        SELECT CAST('<root><r><![CDATA[' + 
            REPLACE(REPLACE(tokens, CHAR(13)+CHAR(10), @separator), @separator, ']]></r><r><![CDATA[') + 
            ']]></r></root>' AS XML) AS c
    ) t1
    CROSS APPLY c.nodes('/root/r/text()') AS t2(x)
),
modified_tokens AS (
    SELECT 
        id,
        CASE 
            WHEN TRIM(',' FROM token) LIKE '[0-9][0-9]-[0-9][0-9][0-9][0-9]%' 
            THEN 'Product No ' + token
            ELSE token
        END AS modified_token
    FROM split_data
)
SELECT 
    id,
    STUFF((
        SELECT ' ' + modified_token
        FROM modified_tokens mt
        WHERE mt.id = sd.id
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(1024)'), 1, 1, '') AS modified_comments
FROM split_data sd
GROUP BY id;

说明 #

1. 用XML将字符串拆分为单个token
2. 对每个token判断格式并替换
3. 通过FOR XML PATH将修改后的token拼接回完整字符串，用STUFF去掉开头多余的空格

优化建议 #

1. 增强匹配精度：如果需要严格匹配两位数字-四位数字的格式（排除后续带其他数字的情况），SQL Server 2017+可使用REGEXP_LIKE（需兼容级别130+）：

   WHERE REGEXP_LIKE(TRIM(',' FROM s.value), '^[0-9]{2}-[0-9]{4}')
   

2. 保留标点格式：当前方案已处理55-9988,这类带逗号的情况，替换后会保留原标点（即Product No 55-9988,），无需额外调整。
3. 高性能替换（SQL Server 2019+）：直接使用REGEXP_REPLACE批量替换，无需拆分拼接，效率更高：

   SELECT 
       id,
       REGEXP_REPLACE(tokens, '([0-9]{2}-[0-9]{4})', 'Product No \1', 1, 0, 'i') AS modified_comments
   FROM @tbl;
   

4. 保留原换行：若需严格保留原字符串的换行，可按行拆分处理，拼接时还原换行符，避免将换行转为空格。

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