CODEWITHSUNDEEP
t-sql
QQping
QQping

Reputation: 1370

Add comma every nth character in value

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

Upvotes: 2

Views: 6026

Answers (5)

Priyesh
Priyesh

Reputation: 11

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(''))

Upvotes: 1

Acree
Acree

Reputation: 11

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

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 2

David Brabant
David Brabant

Reputation: 43499

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)

Upvotes: 2

Pondlife
Pondlife

Reputation: 16240

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.

Upvotes: 1

Related Questions