34,575
社区成员
发帖
与我相关
我的任务
分享
create table tb(id int,content varchar(50))
insert into tb select 1,'1,2,3,4,6,9,10,11'
insert into tb select 2,'1,3,7,9,11,19'
insert into tb select 3,'11,1,2,4,8,20'
go
update a set a.content=b.content
from tb a inner join (
select id,content=substring(replace(','+content+',',',11,',','),2,len(replace(','+content+',',',11,',','))-2)
from tb
where charindex(',11,',','+content+',')>0)b on a.id=b.id
select * from tb
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
go
create table #T (id varchar(2),content varchar(27))
insert into #T
select '1','1,2,3,4,6,9,10,11' union all
select '2','1,3,7,9,11,19' union all
select '3,','1,2,4,8,20' union all
select '4,','1,2,4,8,11,21,11,20'
update #T set content = stuff(reverse(stuff(reverse(replace(','+content+',',',11,',',')),1,1,'')),1,1,'')
select * from #T
/*
id content
1 1,2,3,4,6,9,10
2 1,3,7,9,19
3, 1,2,4,8,20
4, 1,2,4,8,21,20
*/
--这语句得保证你前面和后面,都没有[,]号,这样才能完全正确
create table tb(id int,content varchar(50))
insert into tb select 1,'1,2,3,4,6,9,10,11'
insert into tb select 2,'1,3,7,9,11,19'
insert into tb select 3,'11,1,2,4,8,20'
go
select substring(replace(','+content+',',',11,',','),2,len(replace(','+content+',',',11,',','))-2)
from tb
where charindex(',11,',','+content+',')>0
create table tb(id int,content varchar(50))
insert into tb select 1,'1,2,3,4,6,9,10,11'
insert into tb select 2,'1,3,7,9,11,19'
insert into tb select 3,'1,2,4,8,20'
go
with cte as
(
select a.id,b.col
from (select id,cast('<items><item>'+replace(content,',','</item><item>')+'</item></items>' as xml) as col from tb where charindex(',11,',','+content+',')>0)a
cross apply
(select t.col.value('text()[1]','varchar(50)') as col from a.col.nodes('//item') as t(col))b
)
update a set a.content=b.content
from tb a inner join (
select distinct a.id,stuff(b.col.value('/root[1]','varchar(100)'),1,1,'') as content
from cte a
cross apply
(select col=(select ','+ col from cte where id=a.id and col<>11 for xml path(''),root('root'),type)) b
)b on a.id=b.id
select * from tb