Fastest way to add comma in a string

Question

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?

It's hard to say which option is the fastest without trying out, but maybe this helps you, it's about the same topic: https://stackoverflow.com/questions/10595970/add-comma-every-nth-character-in-value — Jonas Metzler, May 21 '22 at 04:07

Stu · Accepted Answer · 2022-05-21T10:04:57.763

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.

Would this not have troubles because of [What is MySQL row order for "SELECT * FROM table_name;"?](https://stackoverflow.com/questions/1949641/what-is-mysql-row-order-for-select-from-table-name) ? That question is about MySQL, but the reason also applied to MSSQL (or SQL in general). — Luuk, May 21 '22 at 09:31
@Luuk Yes I should add an `order by n` for completeness, I have added above. It's correct that ordering in any resultset is not guaranteed unless specified. In practice selecting from a numbers table's clustered index should follow the table ordering unless things like multiple threads or fragmentation are a factor. Also saying that with the OPs specific example string would it matter as the string segments repeat - although alone not a reason to leave it out of course. — Stu, May 21 '22 at 10:02
In this case, where all records contain `1234567890` it should not matter, But maybe the string in the real-life situation from OP does not follow this simplicity — Luuk, May 21 '22 at 10:19
@Luuk Yes I have no doubt that is true; with the order by clause in the string_agg it is fine however, even the performance is also unnafected with a unique clustered index. — Stu, May 21 '22 at 10:21

RF1991 · Answer 2 · 2022-05-21T11:31:53.130

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

Fastest way to add comma in a string

2 Answers2