SQL Server提取指定格式字符串中分隔段首编码的实现方法

需求说明 #

SQL Server某表的Review列存储了特殊格式的字符串：

• 整体以|分隔多个数据段
• 每个数据段内以:分隔字段
  需要提取每个数据段中第一个:之前的编码，最终用,连接成目标格式的字符串。

输入输出示例 #

输入数据 #

Reviews_Column_Data
'05012:000000:  :0:00000000|00647:000000:  :0:00000000|00283:000000:  :0:00000000|'
'05012:000000:  :0:00000000|00025:000000:  :0:00000000|00647:000000:  :0:00000000|'
'05012:000000:  :0:00000000|02095:000000:  :0:00000000|00647:000000:  :0:00000000|'
'05012:000000:  :0:00000000|00647:000000:  :0:00000000|'
'05081:023931:DF:9:20230111|00604:023931:XX:9:20230111|02470:023931:XX:9:20230111|00655:023931:XX:9:20230111|00464:023931:XX:9:20230111|02130:023931:XX:9:20230111|'
'05081:023931:DF:9:20230131|02229:023931:XX:9:20230131|02130:023931:XX:9:20230131|00692:023931:XX:9:20230131|02170:023931:XX:9:20230131|05084:000000:  :0:00000000|00647:000000:  :0:00000000|'

输出结果 #

Application_Review_Column_Data
'05012,00647,00283'
'05012,00025,00647'
'05012,02095,00647'
'05012,00647'
'05081,00604,02470,00655,00464,02130'
'05081,02229,02130,00692,02170,05084,00647'

用户尝试的代码 #

DROP TABLE IF EXISTS #Temp_Tbl
Create table #Temp_Tbl (Comments varchar(500));

INSERT INTO #Temp_Tbl
VALUES('05012:000000:  :0:00000000|00647:000000:  :0:00000000|00283:000000:  :0:00000000|'),
('05012:000000:  :0:00000000|00025:000000:  :0:00000000|00647:000000:  :0:00000000|'),
('05012:000000:  :0:00000000|02095:000000:  :0:00000000|00647:000000:  :0:00000000|'),
('05081:023931:DF:9:20230131|02229:023931:XX:9:20230131|02130:023931:XX:9:20230131|00692:023931:XX:9:20230131|02170:023931:XX:9:20230131|')

可行实现方案 #

方法一：使用STRING_SPLIT + STRING_AGG（SQL Server 2016及以上版本） #

该方法利用SQL Server内置的字符串拆分和聚合函数，简洁高效：

SELECT 
    STRING_AGG(SUBSTRING(split_val.value, 1, CHARINDEX(':', split_val.value) - 1), ',') AS Application_Review_Column_Data
FROM #Temp_Tbl
CROSS APPLY STRING_SPLIT(TRIM('|' FROM #Temp_Tbl.Comments), '|') AS split_val
WHERE CHARINDEX(':', split_val.value) > 0 -- 过滤无有效分隔符的无效段
GROUP BY #Temp_Tbl.Comments;

代码说明：

1. TRIM('|' FROM Comments)：去除字符串首尾的|，避免拆分后出现空行
2. STRING_SPLIT(..., '|')：将处理后的字符串按|拆分成多行数据
3. SUBSTRING(..., 1, CHARINDEX(':', ...)-1)：提取每个数据段中第一个:之前的编码
4. STRING_AGG(..., ',')：将提取出的编码用,连接成目标格式
5. GROUP BY Comments：按原字符串分组，确保每一行输入对应一行输出
6. WHERE条件：容错处理，过滤掉不含:的无效数据段

方法二：兼容SQL Server 2016以下版本（XML方法） #

如果使用的是SQL Server 2016之前的版本，可通过XML解析实现：

SELECT 
    STUFF(
        (
            SELECT ',' + SUBSTRING(split_val, 1, CHARINDEX(':', split_val) - 1)
            FROM (
                SELECT CAST('<v>' + REPLACE(TRIM('|' FROM Comments), '|', '</v><v>') + '</v>' AS XML) AS xml_val
            ) AS t
            CROSS APPLY (
                SELECT x.v.value('.', 'varchar(100)') AS split_val
                FROM t.xml_val.nodes('/v') AS x(v)
            ) AS split
            WHERE CHARINDEX(':', split.split_val) > 0
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(500)'),
        1, 1, ''
    ) AS Application_Review_Column_Data
FROM #Temp_Tbl;

代码说明：

1. 将字符串转换为XML格式，用<v>标签包裹每个数据段
2. 通过nodes()方法拆分XML为多行数据
3. 提取每个段的编码后，用FOR XML PATH('')拼接成字符串
4. STUFF：去掉拼接后字符串开头的多余逗号

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