如何在SQL Server中实现特定字符串的格式转换

针对你需要将8:live:avishekh.bh 8:prakash.tndk 8:niraj.bajra这类字符串转换为[live:avishekh.bh, prakash.tndk, niraj.bajra]格式的需求，我整理了两种实用的实现方案，适配不同版本的SQL Server：

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

这个方案利用SQL Server 2016引入的原生字符串函数，代码简洁高效：

DECLARE @inputStr NVARCHAR(MAX) = '8:live:avishekh.bh 8:prakash.tndk 8:niraj.bajra';

SELECT '[' + STRING_AGG(STUFF(value, 1, 2, ''), ', ') + ']' AS FormattedResult
FROM STRING_SPLIT(LTRIM(RTRIM(@inputStr)), ' ')
WHERE value <> ''; -- 过滤可能的空值（比如首尾空格或连续空格产生的空项）

代码说明： #

• STRING_SPLIT(@inputStr, ' ')：将输入字符串按空格拆分成独立的行记录，每个行对应一个带8:前缀的子串。
• STUFF(value, 1, 2, '')：移除每个子串开头的8:（从第1个字符开始，删除2个字符，替换为空字符串）。
• STRING_AGG(..., ', ')：将处理后的子串用, 连接成一个完整的字符串。
• 最后手动拼接[和]，得到目标格式。

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

如果你的SQL Server版本低于2016，没有原生的拆分和聚合函数，可以用XML来实现：

DECLARE @inputStr NVARCHAR(MAX) = '8:live:avishekh.bh 8:prakash.tndk 8:niraj.bajra';

-- 先将字符串转换为XML结构
DECLARE @xml XML = '<item>' + REPLACE(LTRIM(RTRIM(@inputStr)), ' ', '</item><item>') + '</item>';

-- 提取节点值、处理前缀并拼接
SELECT '[' + STUFF((
    SELECT ', ' + STUFF(item.value('.', 'NVARCHAR(MAX)'), 1, 2, '')
    FROM @xml.nodes('/item') AS T(item)
    WHERE item.value('.', 'NVARCHAR(MAX)') <> '' -- 过滤空项
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ']' AS FormattedResult;

代码说明： #

• REPLACE(..., ' ', '</item><item>')：将空格替换为XML节点标签，生成包含所有子串的XML结构。
• @xml.nodes('/item')：遍历XML中的每个item节点，提取子串值并去掉8:前缀。
• FOR XML PATH('')：将所有处理后的子串拼接成一个字符串，再用STUFF移除开头多余的, 。
• 最后拼接方括号得到结果。

这两种方案都能完美实现你想要的格式转换，你可以根据自己的SQL Server版本选择合适的方法~

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