Add comma every nth character in value

Question

my problem is pretty simple. I get a value from a sql select which looks like this:

ARAMAUBEBABRBGCNDKDEEEFOFIFRGEGRIEISITJPYUCAKZKG

and I need it like this:

AR,AM,AU,BE,BA,BR,BG,CN,DK,DE,EE,FO,FI,FR,GE,GR,IE,IS,IT,JP,YU,CA,KZ,KG

The length is different in each dataset. I tried it with format(), stuff() and so on but nothing brought me the result I need.

Thanks in advance

Why does this have to be done in SQL? And do you want this to be done over multiple rows in a recordset with different formatting per row? — gbn, May 15 '12 at 07:37
The formatting has to be applied only to one row in the same way. Only the length of the value is different but every value is divisible by 2. And it must be in SQL. Dunno why but my boss wants it that way. — QQping, May 15 '12 at 07:44
Your boss is a muppet then for dictating a *solution* that you *must* use. Good luck. — gbn, May 15 '12 at 07:50
I know hardly anything about sql, but after some quick googling, could you just split the string using `SUBSTRING`, add a comma after each of them using `CONCAT`, and then join them all up again using `CONCAT`? — GravityScore, May 15 '12 at 09:04

score 2 · Answer 1 · answered May 15 '12 at 11:58

Time to update your resume.

create function DontDoThis (
    @string varchar(max),
    @count int
)
returns varchar(max)
as
begin
    declare @result varchar(max) = ''
    declare @token varchar(max) = ''

    while DATALENGTH(@string) > 0
    begin
        select @token = left(@string, @count) 
        select @string = REPLACE(@string, @token, '')
        select @result += @token + case when DATALENGTH(@string) = 0 then '' else ',' end
    end

    return @result
end

Call:

declare @test varchar(max) = 'ARAMAUBEBABRBGCNDKDEEEFOFIFRGEGRIEISITJPYUCAKZKG'
select dbo.DontDoThis(@test, 2)

score 2 · Accepted Answer · edited Jan 18 '21 at 12:30

With a little help of a numbers table and for xml path.

-- Sample table 
declare @T table
(
  Value nvarchar(100)
)

-- Sample data
insert into @T values
('ARAMAU'),
('ARAMAUBEBABRBGCNDKDEEEFOFIFRGEGRIEISITJPYUCAKZKG')


declare @Len int
set @Len = 2;

select stuff(T2.X.value('.', 'nvarchar(max)'), 1, 1, '')
from @T as T1
  cross apply (select ','+substring(T1.Value, 1+Number*@Len, @Len)
               from Numbers
               where Number >= 0 and 
                     Number < len(T1.Value) / @Len
               order by Number
               for xml path(''), type) as T2(X)

Try on SE-Data

score 1 · Answer 3 · answered May 15 '12 at 08:46

1

gbn's comment is exactly right, if not very diplomatic :) TSQL is a poor language for string manipulation, but if you write a CLR function to do this then you will have the best of both worlds: .NET string functions called from pure TSQL.

answered May 15 '12 at 08:46

Pondlife

15,992
6
37
51

Acree · Answer 4 · 2014-07-01T03:39:02.820

I believe this is what QQping is looking for.

-- select .dbo.DelineateEachNth('ARAMAUBEBABRBGCNDKDEEEFOFIFRGEGRIEISITJPYUCAKZKG',2,',')

create function DelineateEachNth
(   
    @str varchar(max), -- Incoming String to parse
    @length int, -- Length of desired segment
    @delimiter varchar(100) -- Segment delimiter (comma, tab, line-feed, etc)
)
returns varchar(max)
AS
begin

    declare @resultString varchar(max) = ''
    -- only set delimiter(s) when lenght of string is longer than desired segment
    if LEN(@str) > @length
    begin
        -- continue as long as there is a remaining string to parse
        while len(@str) > 0
        begin
            -- as long as know we still need to create a segment...
            if LEN(@str) > @length
            begin
                -- build result string from leftmost segment length
                set @resultString = @resultString + left(@str, @length) + @delimiter
                -- continually shorten result string by current segment
                set @str = right(@str, len(@str) - @length)
            end
            -- as soon as the remaining string is segment length or less,
            --  just use the remainder and empty the string to close the loop
            else            
            begin
                set @resultString = @resultString + @str
                set @str = ''
            end
        end
    end
    -- if string is less than segment length, just pass it through
    else
    begin  
        set @resultString = @str
    end
    return @resultString 
end

score 1 · Answer 5 · answered Apr 20 '18 at 08:50

1

With a little help from Regex

select Wow= 
(select case when MatchIndex %2 = 0 and MatchIndex!=0 then ',' + match else match end
  from dbo.RegExMatches('[^\n]','ARAMAUBEBABRBGCNDKDEEEFOFIFRGEGRIEISITJPYUCAKZKG',1)
  for xml path(''))

answered Apr 20 '18 at 08:50

Priyesh

11
3

Add comma every nth character in value

5 Answers5

Linked