求一SQL语句

kkgogo 2006-09-14 08:50:02

删除表中存在**##这样的内容的行，*与#均为数字0-9。例如1122、1133、2233等。谢谢

...全文

196 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

kkgogo 2006-09-14

打赏
举报

谢谢大家，OK啦。结贴

playwarcraft 2006-09-14

打赏
举报

--try
create table t(id varchar(20))
insert into t select '2000601063355487'
insert into t select '2000601061122211'
insert into t select '2000601061345566'
insert into t select '2000601060122344'

go
create function f_s(@id varchar(20))
returns int
as
begin
declare @n int,@r int
set @r=0
set @n=1
while @n<=len(@id)-3
begin
if convert(int,substring(@id,@n,2))%11=0 and convert(int,substring(@id,@n+2,2))%11=0
begin
set @r=@r+1
set @n=@n+1
end
else
set @n=@n+1
end
return @r
end

go

delete t where dbo.f_s(id)>=1

select * from t
/*結果*/
id
--------------------
2000601060122344

hanjie216 2006-09-14

打赏
举报

不对，没看清题意，沙果的方法不错啊，现转化成****，然后再找含有****的就行了

hanjie216 2006-09-14

打赏
举报

delete Table where left(colname,1)=left(colname,2) and left(colname,3)=left(colname,4)

andrew8231 2006-09-14

打赏
举报

to gahade(沙果) :你的这句话delete from # where code like '%[00-99]%'中"[ ]"是不是什么意思？-

gahade 2006-09-14

打赏
举报

比较麻烦的实现

create table #(code varchar(100),code1 varchar(100))
insert into #(code)
select '2000601063355487'
union all select '2000601061122211'
union all select '2000601061345566'
union all select '2000601061123368'
union all select '2123601061234567'
update #
set code1 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(code,'00','**'),'11','**'),'22','**'),'33','**'),'44','**'),'55','**'),'66','**'),'77','**'),'88','**'),'99','**')
select code from # where charindex('****',code1)>0

drop table #

kkgogo 2006-09-14

打赏
举报

还有一个，前面的200也不固定，所以不能截取后若干位来匹配

kkgogo 2006-09-14

打赏
举报

例如：
2000601063355487
2000601061122211
2000601061345566
2000601061123368
前三条都符合**##，最后一条不符。

anchor49 2006-09-14

打赏
举报

谢谢！！！

gahade 2006-09-14

打赏
举报

create table #(code varchar(100))
insert into #(code)
select 'abc1122&#*'
union all select '78351133#%DJ'
union all select 'DFsdfsdf2233'
union all select 'UIUWEIUNIDFE'
delete from # where code like '%[00-99]%'
drop table #

playwarcraft 2006-09-14