select * from tb --ABAB where len(num)=11 and substring(num,8,1)= substring(num,10,1) and substring(num,9,1)= substring(num,11,1) union select * from tb --AABB where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,11,1) union select * from tb --ABC where len(num)=11 and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1) union select * from tb --ABCD where len(num)=11 and cast(substring(num,8,1) as int)= (cast(substring(num,9,1)as int) +1) and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1)
呵呵,谢谢大侠,继续帮我写完可以吗?因为四年没接触过SQL现在忘光了。以前也只懂得简单的。
-- ABAB: select * from tb where len(hm)=11 and substring(hm,8,1)= substring(hm,10,1)-- AABBselect * from tb where len(hm)=11 and substring(hm,8,1)= substring(hm,9,1) and substring(hm,10,1)= substring(hm,11,1) and substring(hm,8,1)<> substring(hm,10,1)
-- ABC select * from tb where len(hm)=11 and substring(hm,9,1)<>substring(hm,10,1) and substring(hm,10,1)<> substring(hm,11,1) -- ABCD select * from tb where len(hm)=11 and substring(hm,8,1) not in(substring(hm,9,1),substring(hm,10,1),substring(hm,11,1)) and substring(hm,9,1)not in(substring(hm,10,1),substring(hm,11,1)) and substring(hm,10,1)<> substring(hm,11,1) -- AAAB select * from tb where len(hm)=11 and substring(hm,8,1)=(substring(hm,9,1) and substring(hm,9,1)= substring(hm,10,1) and substring(hm,10,1)<> substring(hm,11,1) -- AABBCC select * from tb where len(hm)=11 and substring(hm,6,1)=(substring(hm,7,1) and substring(hm,8,1)=(substring(hm,9,1) and substring(hm,10,1)=substring(hm,11,1) and substring(hm,6,1)NOT IN(substring(hm,8,1) ,substring(hm,10,1)) AND substring(hm,8,1)<>substring(hm,10,1)-- AAA select * from tb where len(hm)=11 and substring(hm,9,1)=substring(hm,10,1) and substring(hm,10,1)=substring(hm,11,1)-- AAABBBselect * from tb where len(hm)=11 and substring(hm,6,1)=(substring(hm,7,1) and substring(hm,7,1)=(substring(hm,8,1) and substring(hm,9,1)=(substring(hm,10,1) and substring(hm,10,1)=substring(hm,11,1) AND substring(hm,6,1)<>substring(hm,9,1)--AAAA select * from tb where len(hm)=11 and substring(hm,9,1)=substring(hm,8,1) and substring(hm,9,1)=substring(hm,10,1) and substring(hm,10,1)=substring(hm,11,1)--AAAB最后一个,你自己写。。
select * from tb --ABAB where len(num)=11 and substring(num,8,1)= substring(num,10,1) and substring(num,9,1)= substring(num,11,1) union select * from tb --AABB where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,11,1) union select * from tb --ABC where len(num)=11 and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1) union select * from tb --ABCD where len(num)=11 and cast(substring(num,8,1) as int)= (cast(substring(num,9,1)as int) +1) and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1) union select * from tb --AAAB where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1) union select * from tb --AABBCC where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,11,1) and substring(num,6,1)= substring(num,7,1) union select * from tb --AAA where len(num)=11 and substring(num,11,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1) union select * from tb --AAABBB where len(num)=11 and substring(num,11,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1) and substring(num,6,1)= substring(num,8,1) and substring(num,6,1)= substring(num,7,1) union select * from tb --AAAA where len(num)=11 and substring(num,11,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1) and substring(num,8,1)= substring(num,9,1) union select * from tb --AAAB where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1)
可以使用循环啊 declare @i int declare @j int set @i=0 set @j=0while @i<=9 begin --abab while @j<=9 begin if(@i=0) insert into lianghao select '0'+cast(@j as varchar(1))+'0'+cast(@j as varchar(1)) else begin if(@i<>@j) begin insert into lianghao select cast(@i*1000+@j*100+@i*10+@j as varchar(10)) end end set @j=@j+1 end set @i=@i+1 end --同理
楼主的问题很有意思,下面是我的完整的解决方案--建表,插演示数据 create table tb(id int identity, code varchar(20)) insert into tb select 13312348989 union all select 13312348889 union all select 13312348899 union all select 13312349988 union all select 13312344244 union all select 13312344333 union all select 13312333444 go--建立函数,返回指定位数特征码。如8989返回ABAB if object_id('GetChar','FN') is not null drop function GetChar gocreate function dbo.GetChar(@code varchar(20),@num int) returns varchar(10) as begin if @num < 2 or @num > 10 return '' declare @ret varchar(10) declare @t table(row int, num int) declare @i int, @j int,@s varchar(20) set @i = 2 set @j = 0 set @s = right(@code,@num) set @ret = 'A' insert @t values(1,0) while @i <= @num begin if charindex(substring(@s,@i,1),@s) between 1 and @i-1 begin set @ret = @ret + char(64+charindex(substring(@s,@i,1),@s)-(select num from @t where row = charindex(substring(@s,@i,1),@s))) set @j = @j + 1 end else set @ret = @ret + char(64-@j+@i) insert @t values(@i,@j) set @i = @i + 1 end return @ret end --返回所有手机号码的后四位/后五位特征码 select *,'后四位' = dbo.GetChar(code,4),'后五位' = dbo.GetChar(code,5) from tb /* id code 后四位 后五位 ----------- -------------------- ---------- ---------- 1 13312348989 ABAB ABCBC 2 13312348889 AAAB ABBBC 3 13312348899 AABB ABBCC 4 13312349988 AABB ABBCC 5 13312344244 ABAA AABAA 6 13312344333 ABBB AABBB 7 13312333444 ABBB AABBB(7 行受影响) */--返回后四位特征码是AABB的手机号 select * from tb where dbo.GetChar(code,4) = 'AABB' /* id code ----------- -------------------- 3 13312348899 4 13312349988(2 行受影响) */ 楼主,记得结贴哦,还有,1K分哦!
ABAB:
select * from tb
where len(hm)=11
and substring(hm,8,1)= substring(hm,10,1)
select * from tb1 where right(手机号,3) in (select haoma from tb2) or right(手机号,4) in (select haoma from tb2)
1212
1313
1414
...
1919
2010
2121
2323
...
2929把你存在的所有可能的号码做成一个表。
select * from tb --ABAB
where len(num)=11 and substring(num,8,1)= substring(num,10,1) and substring(num,9,1)= substring(num,11,1)
union
select * from tb --AABB
where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,11,1)
union
select * from tb --ABC
where len(num)=11 and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1)
union
select * from tb --ABCD
where len(num)=11 and cast(substring(num,8,1) as int)= (cast(substring(num,9,1)as int) +1) and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1)
select * from tb
where len(hm)=11
and substring(hm,8,1)= substring(hm,10,1)-- AABBselect * from tb
where len(hm)=11
and substring(hm,8,1)= substring(hm,9,1)
and substring(hm,10,1)= substring(hm,11,1)
and substring(hm,8,1)<> substring(hm,10,1)
-- ABC
select * from tb
where len(hm)=11
and substring(hm,9,1)<>substring(hm,10,1)
and substring(hm,10,1)<> substring(hm,11,1)
-- ABCD
select * from tb
where len(hm)=11
and substring(hm,8,1) not in(substring(hm,9,1),substring(hm,10,1),substring(hm,11,1))
and substring(hm,9,1)not in(substring(hm,10,1),substring(hm,11,1))
and substring(hm,10,1)<> substring(hm,11,1)
-- AAAB
select * from tb
where len(hm)=11
and substring(hm,8,1)=(substring(hm,9,1)
and substring(hm,9,1)= substring(hm,10,1)
and substring(hm,10,1)<> substring(hm,11,1) -- AABBCC
select * from tb
where len(hm)=11
and substring(hm,6,1)=(substring(hm,7,1)
and substring(hm,8,1)=(substring(hm,9,1)
and substring(hm,10,1)=substring(hm,11,1)
and substring(hm,6,1)NOT IN(substring(hm,8,1) ,substring(hm,10,1))
AND substring(hm,8,1)<>substring(hm,10,1)-- AAA
select * from tb
where len(hm)=11
and substring(hm,9,1)=substring(hm,10,1)
and substring(hm,10,1)=substring(hm,11,1)-- AAABBBselect * from tb
where len(hm)=11
and substring(hm,6,1)=(substring(hm,7,1)
and substring(hm,7,1)=(substring(hm,8,1)
and substring(hm,9,1)=(substring(hm,10,1)
and substring(hm,10,1)=substring(hm,11,1)
AND substring(hm,6,1)<>substring(hm,9,1)--AAAA
select * from tb
where len(hm)=11
and substring(hm,9,1)=substring(hm,8,1)
and substring(hm,9,1)=substring(hm,10,1)
and substring(hm,10,1)=substring(hm,11,1)--AAAB最后一个,你自己写。。
select * from tb --ABAB
where len(num)=11 and substring(num,8,1)= substring(num,10,1) and substring(num,9,1)= substring(num,11,1)
union
select * from tb --AABB
where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,11,1)
union
select * from tb --ABC
where len(num)=11 and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1)
union
select * from tb --ABCD
where len(num)=11 and cast(substring(num,8,1) as int)= (cast(substring(num,9,1)as int) +1) and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1)
union
select * from tb --AAAB
where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1)
union
select * from tb --AABBCC
where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,11,1) and substring(num,6,1)= substring(num,7,1)
union
select * from tb --AAA
where len(num)=11 and substring(num,11,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1)
union
select * from tb --AAABBB
where len(num)=11 and substring(num,11,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1)
and substring(num,6,1)= substring(num,8,1) and substring(num,6,1)= substring(num,7,1)
union
select * from tb --AAAA
where len(num)=11 and substring(num,11,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1) and substring(num,8,1)= substring(num,9,1)
union
select * from tb --AAAB
where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1)
declare @i int
declare @j int
set @i=0
set @j=0while @i<=9
begin
--abab
while @j<=9
begin
if(@i=0)
insert into lianghao select '0'+cast(@j as varchar(1))+'0'+cast(@j as varchar(1))
else
begin
if(@i<>@j)
begin
insert into lianghao select cast(@i*1000+@j*100+@i*10+@j as varchar(10))
end
end
set @j=@j+1
end
set @i=@i+1
end
--同理
create table tb(id int identity, code varchar(20))
insert into tb
select 13312348989 union all
select 13312348889 union all
select 13312348899 union all
select 13312349988 union all
select 13312344244 union all
select 13312344333 union all
select 13312333444
go--建立函数,返回指定位数特征码。如8989返回ABAB
if object_id('GetChar','FN') is not null
drop function GetChar
gocreate function dbo.GetChar(@code varchar(20),@num int)
returns varchar(10)
as
begin
if @num < 2 or @num > 10
return '' declare @ret varchar(10)
declare @t table(row int, num int)
declare @i int, @j int,@s varchar(20)
set @i = 2
set @j = 0
set @s = right(@code,@num) set @ret = 'A'
insert @t values(1,0)
while @i <= @num
begin
if charindex(substring(@s,@i,1),@s) between 1 and @i-1
begin
set @ret = @ret + char(64+charindex(substring(@s,@i,1),@s)-(select num from @t where row = charindex(substring(@s,@i,1),@s)))
set @j = @j + 1
end
else
set @ret = @ret + char(64-@j+@i) insert @t values(@i,@j)
set @i = @i + 1
end return @ret
end
--返回所有手机号码的后四位/后五位特征码
select *,'后四位' = dbo.GetChar(code,4),'后五位' = dbo.GetChar(code,5) from tb
/*
id code 后四位 后五位
----------- -------------------- ---------- ----------
1 13312348989 ABAB ABCBC
2 13312348889 AAAB ABBBC
3 13312348899 AABB ABBCC
4 13312349988 AABB ABBCC
5 13312344244 ABAA AABAA
6 13312344333 ABBB AABBB
7 13312333444 ABBB AABBB(7 行受影响)
*/--返回后四位特征码是AABB的手机号
select * from tb where dbo.GetChar(code,4) = 'AABB'
/*
id code
----------- --------------------
3 13312348899
4 13312349988(2 行受影响)
*/
楼主,记得结贴哦,还有,1K分哦!