SQLTeam.com | Weblogs | Forums

Insert a series of numbers after every nth space in a string


#1

Hi, I have a field that contains a space-separated string of numbers such as 2 2 2 2 2 2 2 2. I trying to figure out how to insert 1 1 1 1 after every 2nd space to get back a string like such: 2 2 1 1 1 1 2 2 1 1 1 1 2 2 1 1 1 1 2 2 1 1 1 1. I’m using SQL 2008 and have the ability to split the string by space into a table and do loops and inserts to reassemble it as necessary, but I was hoping to find a more elegant solution.
Any help will be appreciated.
Thanks


#2

Using the awesome DelimitedSplit8K by @JeffModen, you can do something like:

create table #yourtable(yourfield varchar(100));
insert into #yourtable(yourfield) values
  ('2 2 2 2 2 2 2 2')
 ,('33 33 33 33 33 33 33 33 33 33 33 33 33 33 33 33')
;

declare @delim char=' ';
declare @every int=3;
declare @stuffvalue varchar(100)='1 1 1 1';

select stuff((select b.item
                   +case when b.itemnumber%@every=0 then c.v else ' ' end
              from delimitedsplit8K(a.yourfield,@delim) as b
                   cross apply (select @delim+@stuffvalue+@delim as v) as c
             order by b.itemnumber
               for xml path('')
            )
            ,1
            ,0
            ,''
           )
 from #yourtable as a
;

drop table #yourtable;


#3

Thanks for the response!