数据处理的问题

[sy_binbin]

现在一个表里有N多条数据，例如

id content
1 1,2,3,4,6,9,10,11
2 1,3,7,9,11,19
3, 1,2,4,8,20


我要求把含有11的数据都给去掉，最后变成

id content
1 1,2,3,4,6,9,10
2 1,3,7,9,19
3, 1,2,4,8,20


这样用SQL语句怎么写？？

[pl_mm]

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 1,'1,3,7,9,11,19'
insert into tb select 1,'1,2,4,8,20'

update tb set content=replace(','+content+',',',11,','')

select * from tb

[Andy__Huang]

select id,replace(','+content+',',',11,',',') from tb

[让你望见影子的墙]

--> (让你望见影子的墙)生成测试数据，时间：2009-02-17
 
if not object_id('tb') is null
	drop table tb
Go
Create table tb([id] nvarchar(2),[content] nvarchar(17))
Insert tb
select N'1',N'1,2,3,4,6,9,10,11' union all
select N'2',N'1,3,7,9,11,19' union all
select N'3',N'1,2,4,8,20'
Go
Select * from tb

update tb
set content=replace(content,',11','')
where charindex('11',content)>0
select * from tb

1	1,2,3,4,6,9,10
2	1,3,7,9,19
3	1,2,4,8,20

[sy_binbin]

[Quote=引用 1 楼 pl_mm 的回复:]
SQL codecreate table tb(id int,content varchar(50))
insert into tb select 1,'1,2,3,4,6,9,10,11'
insert into tb select 1,'1,3,7,9,11,19'
insert into tb select 1,'1,2,4,8,20'

update tb set content=replace(','+content+',',',11,','')

select * from tb
[/Quote]
你的不对

每条数据前面都多了一个逗号，另外'1,3,7,9,11,19'这条数据处理后的结果也不对

[Andy__Huang]

--drop table tb

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 1,'1,3,7,9,11,19'
insert into tb select 1,'1,2,4,8,20'

select id,substring(replace(','+content+',',',11,',','),2,len(replace(','+content+',',',11,',','))-2)
from tb
/*
1 1,2,3,4,6,9,10
1 1,3,7,9,19
1 1,2,4,8,20
*/

[nzperfect]

select id,substring(replace(','+content+',',',11,',','),2,len(content)-3) from tb

[nzperfect]

select id,stuff(reverse(stuff(reverse(replace(','+content+',',',11,',',')),1,1,'')),1,1,'') from tb

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[content] varchar(17))
insert [tb]
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'
 
---查询---
select 
  id,
  replace(replace(content,',11',''),'11,','') as content 
from [tb]

---结果---
id          content                                                                                                                                                                                                                                                          
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           1,2,3,4,6,9,10
2           1,3,7,9,19
3           1,2,4,8,20

（所影响的行数为 3 行）

[让你望见影子的墙]

稍微改一下，增加了11在数据第一个位置的处理


--> (让你望见影子的墙)生成测试数据，时间：2009-02-17
 
if not object_id('tb') is null
	drop table tb
Go
Create table tb([id] nvarchar(2),[content] nvarchar(17))
Insert tb
select N'1',N'1,2,3,4,6,9,10,11' union all
select N'2',N'1,3,7,9,11,19' union all
select N'3',N'11,1,2,4,8,20'
Go
Select * from tb

update tb
set content=case when charindex('11',content)=1 then replace(content,'11,','')
                 else replace(content,',11','') end
where charindex('11',content)>0

select * from tb

1	1,2,3,4,6,9,10
2	1,3,7,9,19
3	1,2,4,8,20

[nzperfect]

bs楼主一次.

[sy_binbin]

[Quote=引用 7 楼 perfectaction 的回复:]
select id,stuff(reverse(stuff(reverse(replace(','+content+',',',11,',',')),1,1,'')),1,1,'') from tb
[/Quote]


不要BS我啊，虽然你的也能实现，但我感觉有些复杂