Fastest way to add comma in a string-CodePudding

I have a long string stored in a table in SQL Server and I want to add a "comma" in between every 10 chars.

I did that with the help of while statement but it is so slow.

Is there any faster way to do that?

Here is what I did

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

Working with a big table this method is too slow.

Is there any faster way?

CodePudding user response：

Using a numbers table would be the best way to implement this.

You need to remember SQL Server is desiged to work with sets of rows. If you find yourself looping -always stop and think, could you approach the problem as a collection of rows?

The following implements a numbers table using a CTE (this would be a permanant table with rows from 1 up to whatever you need).

You can then select rows from the numbers table to match your requirements, in this case every 10th row using modulo, selecting the relevant substring portion. string_agg can then combine the rows back into a single string, automatically adding the comma separator.

The stuff function adds a comma to the beginning of the string like your own output produces, leave this off if that's not required.

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);

Performance comparison

Using a suitably long string to test with (320k characters), the looping method takes over 3 seconds.

The same result using the numbers table implementation above takes about 94 milliseconds, so approximately 34x faster.

See this demo fiddle with timings.

CodePudding user response：

by using accepted answer of this post and using String_agg and 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