在字符串中添加逗号的最快方法

Question

我有一个长字符串存储在SQL Server表中，我想添加一个"逗号"在每10个字符之间。

我是在while语句的帮助下完成的，但是它太慢了。

有更快的方法吗?

我是这么做的

declare @a varchar(max) = '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'

declare @i int = 1
declare @m int = 10
declare @ai varchar(max) = ''
while @i < len(@a)
begin
set @ai = @ai + ',' + SUBSTRING(@a, @i, @m)
set @i = @i + @m 
end
SELECT @ai

对于一个大的表，这个方法太慢了。

有更快的方法吗?

Answer 1

使用数字表将是实现此功能的最佳方法。

您需要记住SQL Server被设计为使用行集。如果你发现自己在循环——总是停下来想一想，你能不能把这个问题当作行的集合来处理?

下面的代码使用CTE实现了一个数字表(这将是一个永久表，包含从1到您需要的任何行)。

然后，您可以从数字表中选择行来匹配您的需求，在本例中，使用对取模，选择相关的子字符串部分，每隔10行选择行。然后，string_agg可以将这些行合并回单个字符串，并自动添加逗号分隔符。 stuff函数在字符串的开头添加一个逗号，就像您自己的输出一样，如果不需要，请忽略它。

declare @a varchar(max) = '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
with n as (select top(Len(@a)) n = Row_Number() over(order by (select null)) from master.sys.objects)
select Stuff(String_Agg(Substring(@a,n, 10),',') within group (order by n),1,0,',')
from n
where (n-1) % 10 = 0 and n <= Len(@a);

性能比较

使用合适的长字符串来测试(320k个字符)，循环方法需要超过3秒。

使用上面的数字表实现得到相同的结果大约需要94毫秒，所以大约快了34倍。

查看这个演示如何调整的时间。

Answer 2

使用this post的接受答案，使用String_agg和NTILE functions

DECLARE @test VARCHAR(max) = '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' 

SELECT   String_agg( value, ',')
FROM     (
SELECT   String_agg( value, '') within GROUP ( ORDER BY nt1 ASC) value,
nt,
1 AS t
FROM    (
SELECT   substring(a.b, v.number+1, 1) value,
ntile(18) OVER( ORDER BY (
SELECT NULL) ) nt,
row_number() OVER (ORDER BY (
SELECT NULL) ) nt1
FROM     (
SELECT @test b) a
JOIN     master..spt_values v
ON       v.number < len(a.b)
WHERE    v.type = 'P') c
GROUP BY nt ) d
GROUP BY t 

dbfiddle