-- 数字规律 ABC -- 建函数 create function dbo.fn_ABC (@x varchar(16)) returns bit as begin declare @i int,@r bit select @i=1,@r=0
while(@i<=len(@x)-2) begin if (cast(substring(@x,@i,3) as int)-12)%111=0 begin select @r=1 break end select @i=@i+1 end
return @r end
-- 调用 where dbo.fn_ABC([号码字段])=1借用唐诗版主写的。
-- 调用 where dbo.fn_ABC([号码字段])=0
-- 调用 where dbo.fn_ABC([号码字段])=1 这个啥意思
这是函数调用, select 字段 from 表 where dbo.fn_ABC([号码字段])=0 出来的就是你上面想要筛选的结果
IF object_id('dbo.fn_ABC') IS NOT NULL DROP FUNCTION dbo.fn_ABC go create function dbo.fn_ABC (@x varchar(16)) returns bit as begin declare @i int,@r bit select @i=1,@r=0
while(@i<=len(@x)-2) begin if (cast(substring(@x,@i,3) as int)-12)%111=0 begin select @r=1 break end select @i=@i+1 end
return @r end
goselect * from [tb] where dbo.fn_ABC([no])=0
select * from [tb] where dbo.fn_ABC([no])=1后面等于1 调用查不出来吗?
create table a(id int) insert into a(id) values(3155495),(3211654),(6568214),(2546413),(8815155),(2316581),(3521852),(2356462)declare @id varchar(10)= '12341511' ,@i int;with _a as( select substring(cast(id as varchar(10)) ,1 ,3) a1 from a union select substring(cast(id as varchar(10)) ,2 ,3) a1 from a union select substring(cast(id as varchar(10)) ,3 ,3) a1 from a union select substring(cast(id as varchar(10)) ,4 ,3) a1 from a union select substring(cast(id as varchar(10)) ,5 ,3) a1 from a ) ,_b as ( select substring(cast(@id as varchar(10)) ,5 ,3) a1 union select substring(cast(@id as varchar(10)) ,5 ,3) a1 union select substring(cast(@id as varchar(10)) ,5 ,3) a1 union select substring(cast(@id as varchar(10)) ,5 ,3) a1 union select substring(cast(@id as varchar(10)) ,5 ,3) a1 ) select @i = count(1) from _a join _b on _a.a1 = _b.a1 select @idrop table a 当查询结果为0 就是你需要的数据
-- 建函数
create function dbo.fn_ABC
(@x varchar(16)) returns bit
as
begin
declare @i int,@r bit
select @i=1,@r=0
while(@i<=len(@x)-2)
begin
if (cast(substring(@x,@i,3) as int)-12)%111=0
begin
select @r=1
break
end
select @i=@i+1
end
return @r
end
-- 调用
where dbo.fn_ABC([号码字段])=1借用唐诗版主写的。
where dbo.fn_ABC([号码字段])=0
where dbo.fn_ABC([号码字段])=1
这个啥意思
这是函数调用, select 字段 from 表 where dbo.fn_ABC([号码字段])=0 出来的就是你上面想要筛选的结果
IF object_id('dbo.fn_ABC') IS NOT NULL
DROP FUNCTION dbo.fn_ABC
go
create function dbo.fn_ABC
(@x varchar(16)) returns bit
as
begin
declare @i int,@r bit
select @i=1,@r=0
while(@i<=len(@x)-2)
begin
if (cast(substring(@x,@i,3) as int)-12)%111=0
begin
select @r=1
break
end
select @i=@i+1
end
return @r
end
goselect * from [tb] where dbo.fn_ABC([no])=0
insert into a(id) values(3155495),(3211654),(6568214),(2546413),(8815155),(2316581),(3521852),(2356462)declare @id varchar(10)= '12341511' ,@i int;with _a as(
select substring(cast(id as varchar(10)) ,1 ,3) a1
from a
union
select substring(cast(id as varchar(10)) ,2 ,3) a1
from a
union
select substring(cast(id as varchar(10)) ,3 ,3) a1
from a
union
select substring(cast(id as varchar(10)) ,4 ,3) a1
from a
union
select substring(cast(id as varchar(10)) ,5 ,3) a1
from a
)
,_b as (
select substring(cast(@id as varchar(10)) ,5 ,3) a1
union
select substring(cast(@id as varchar(10)) ,5 ,3) a1
union
select substring(cast(@id as varchar(10)) ,5 ,3) a1
union
select substring(cast(@id as varchar(10)) ,5 ,3) a1
union
select substring(cast(@id as varchar(10)) ,5 ,3) a1
)
select @i = count(1) from _a join _b on _a.a1 = _b.a1
select @idrop table a 当查询结果为0 就是你需要的数据