请教如何在一个字符串有分割符的某个位置插入字符串
比如：
有一条记录：111111,222222,333333,444444,555555
问题1：如何在 每个分割符的一个字符串中的中间加上“GO”
使结果变成：
111GO111,222GO222,333GO333,444GO444,555GO555
问题2:已有变量 @name=222222
如何在 当前匹配字符串的中的中间加上“GO”
使结果变成：
111111,222GO222,333333,444444,555555
因为两个问题有关联，所以开到一个帖子了，每个30分,请高人帮忙！
[解决办法]
create table tb(id int identity(1,1),col varchar(40))
insert into tb
select '111111,222222,33333,444444,555555'
go
declare @col varchar(20)
set @col = '222222'
-- 1
;with ach as
(
select a.id,substring(a.col,b.number,charindex(',',a.col+',',b.number)-b.number) as col
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.col)
and substring(','+a.col,b.number,1) = ','
)
select id,stuff(
(select ','+left(col,len(col)/2)+'GO'+right(col,len(col)-len(col)/2)
from ach where id = t.id for xml path('')),
1,1,'') as col
from ach t
group by id
-- 2
select id,replace(col,','+@col+',',
','+left(@col,len(@col)/2)+'GO'+right(@col,len(@col)-len(@col)/2)+',') as col
from tb
drop table tb
/*****************
id col
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 111GO111,222GO222,33GO333,444GO444,555GO555
(1 行受影响)
id col
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 111111,222GO222,33333,444444,555555
(1 行受影响)
declare @t table (col varchar(40))
insert into @t
select '111111,222222,33333,444444,555555'
declare @name varchar(20)
set @name='111111' --111111
update @t set col=
substring(replace(','+col+',',','+@name+',',
','+left(@name,1)+'GO'+right(@name,len(@name)-1)+','),2,
len(replace(','+col+',',','+@name+',',
','+left(@name,1)+'GO'+right(@name,len(@name)-1)+','))-2)
where charindex(','+@name+',',','+col+',')>0
select * from @t
/*
1GO11111,222222,33333,444444,555555
*/
