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
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;
Thanks for the response!