34,838
社区成员




declare @s varchar(10)
set @s = '13'
select id,replace(字段A,','+@s+',','') as 字段A
from ta
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,字段A varchar(100))
insert into #T
select 1,'10,13,23,33' union all
select 2,'11,12,13' union all
select 3,'13,15,18'
update #T set 字段A = replace(字段A, ',13,', ',') where charindex(',13,',字段A)>0
update #T set 字段A = left(字段A, len(字段A) - 3) where right(字段A, 3) = ',13'
update #T set 字段A = right(字段A, len(字段A) - 3) where left(字段A, 3) = '13,'
select * from #T
/*
ID 字段A
1 10,23,33
2 11,12
3 15,18
*/
create table #T (ID int,字段A varchar(100))
insert into #T
select 1,'10,13,13,13,13,23,33' union all
select 2,'11,132,13' union all
select 3,'13,15,138'
select top 2000 id=identity(int,1,1) into # from syscolumns a,syscolumns b
select a.id,
字段A=substring(a.字段A,b.id,charindex(',',a.字段A+',',b.id)-b.id)
into tTest
from #T a, # b
where substring(','+a.字段A,b.id,1)=','
order by a.id,b.id
go
create function getstr(@content varchar(100),@i int)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+ltrim(字段A) from tTest where id=@content and 字段A <> @i
select @str=right(@str,len(@str)-1)
return @str
end
go
select id,dbo.getstr(id,13) as 字段A
from tTest
group by id
drop table #T,#,tTest
drop function getstr
/*
id 字段A
----------- -----------
1 10,23,33
2 11,132
3 15,138
(所影响的行数为 3 行)
*/
create table #T (ID int,字段A varchar(100))
insert into #T
select 1,'10,13,13,13,13,23,33' union all
select 2,'11,132,13' union all
select 3,'13,15,138'
while exists (select 1 from #T where charindex(',13,',字段A) > 0)
update #T set 字段A = replace(字段A, ',13,', ',') where charindex(',13,', 字段A) > 0
create table #T (ID int,字段A varchar(100))
insert into #T
select 1,'10,13,23,33' union all
select 2,'11,132,13' union all
select 3,'13,15,138'
declare @s varchar(10)
set @s = '13'
select id,substring(reverse(stuff(reverse(replace(','+字段A+',',','+@s+',',',')),1,1,'')),2,100) as 字段A
from #T
drop table #T
/*
id 字段A
----------- -----------
1 10,23,33
2 11,12
3 15,138
(所影响的行数为 3 行)
*/
create table #T (ID int,字段A varchar(100))
insert into #T
select 1,'10,13,23,33' union all
select 2,'11,12,13' union all
select 3,'13,15,18'
declare @s varchar(10)
set @s = '13'
select id,stuff(replace(','+字段A,','+@s,''),1,1,'') as 字段A
from #T
drop table #T
/*
id 字段A
----------- -----------
1 10,23,33
2 11,12
3 15,18
(所影响的行数为 3 行)
*/