SQL Server返回格式化电话号码函数 解决拼接末尾多余逗号问题

需求说明 #

构建SQL Server函数，从CONTACTS表提取值班人员电话号码，按规则格式化后供寻呼系统API调用，格式要求：

• 返回值可包含任意数量电话号码
• 每个号码用双引号包裹
• 号码之间用逗号分隔

现有游标实现代码 #

BEGIN
DECLARE @MyRESULT varchar(max)
DECLARE @PAGING_RESULTS nvarchar(max)
DECLARE MyCursor CURSOR FOR
SELECT PHONE_NUMBER FROM CONTACTS WHERE ON_CALL = 1
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @MyRESULT
WHILE @@FETCH_STATUS = 0
BEGIN
Set @PAGING_RESULTS = isnull(@PAGING_RESULTS, '') + '"%2B' + isnull(@MyRESULT, '') +  '",'
FETCH NEXT FROM MyCursor INTO @MyRESULT
END
Close MyCursor
deallocate MyCursor
Set @PAGING_RESULTS = isnull(@PAGING_RESULTS, '')
return @PAGING_RESULTS
END

存在问题 #

现有函数逻辑基本满足需求，但拼接时会在结果末尾多出一个多余逗号，导致API调用失败。

测试样例数据 #

预期返回结果 #

"1234567890", "9876543210", "7652341890"

当前错误返回 #

"1234567890", "9876543210", "7652341890",

解决方案 #

方案1：最小改动修复现有游标逻辑 #

不需要重构原有逻辑，只需要在游标释放后、返回结果前新增一段逻辑，判断结果非空时移除最后一位的多余逗号即可，修改后完整代码：

BEGIN
DECLARE @MyRESULT varchar(max)
DECLARE @PAGING_RESULTS nvarchar(max)
DECLARE MyCursor CURSOR FOR
SELECT PHONE_NUMBER FROM CONTACTS WHERE ON_CALL = 1
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @MyRESULT
WHILE @@FETCH_STATUS = 0
BEGIN
Set @PAGING_RESULTS = isnull(@PAGING_RESULTS, '') + '"%2B' + isnull(@MyRESULT, '') +  '",'
FETCH NEXT FROM MyCursor INTO @MyRESULT
END
Close MyCursor
deallocate MyCursor

-- 移除末尾多余逗号
IF LEN(@PAGING_RESULTS) > 0
    SET @PAGING_RESULTS = LEFT(@PAGING_RESULTS, LEN(@PAGING_RESULTS) - 1)

Set @PAGING_RESULTS = isnull(@PAGING_RESULTS, '')
return @PAGING_RESULTS
END

方案2：无游标高性能实现（推荐） #

游标遍历性能较差，尤其是数据量较大时，可使用SQL Server内置的字符串聚合函数直接拼接，从根源避免末尾逗号问题：

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

代码最简洁，性能最优：

BEGIN
DECLARE @PAGING_RESULTS nvarchar(max)
SELECT @PAGING_RESULTS = STRING_AGG('"%2B' + PHONE_NUMBER + '"', ', ')
FROM CONTACTS 
WHERE ON_CALL = 1

RETURN ISNULL(@PAGING_RESULTS, '')
END

适用于SQL Server 2016及更早版本（无STRING_AGG）

使用FOR XML PATH方式实现拼接，兼容老版本SQL Server：

BEGIN
DECLARE @PAGING_RESULTS nvarchar(max)
SELECT @PAGING_RESULTS = STUFF(
    (SELECT ', ' + '"%2B' + PHONE_NUMBER + '"'
     FROM CONTACTS WHERE ON_CALL = 1
     FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'),
1, 2, '')

RETURN ISNULL(@PAGING_RESULTS, '')
END

无游标方案不需要手动处理末尾逗号，执行效率远高于游标实现，推荐优先使用。

内容的提问来源于stack exchange，提问作者andrew.reynolds81