sql语句实现电话号筛选,例如:AABB,ABAB
项目
数据库为SQL Server 要求实现电话号筛选:
第一中类型: ABAB
Sql代码
select * from telephone where SUBSTRING(telenumber,8,2)=SUBSTRING(telenumber,10,2) and SUBSTRING(telenumber,8,1)!=SUBSTRING(telenumber,11,1);
查询结果:
telenumber
------------
130****1212
130****2323
130****3434
www.2cto.com
第二种类型: AABB
Sql代码
select * from telephone where SUBSTRING(telenumber,8,1)=SUBSTRING(telenumber,9,1) and SUBSTRING(telenumber,10,1)=SUBSTRING(telenumber,11,1) and SUBSTRING(telenumber,8,1)!=SUBSTRING(telenumber,11,1);
查询结果:
telenumber
------------
189****1122
189****3344
189****5566
第三种类型: AAAB
Sql代码
select * from telephone where SUBSTRING(telenumber,8,1)=SUBSTRING(telenumber,9,1) and SUBSTRING(telenumber,9,1)=SUBSTRING(telenumber,10,1) and SUBSTRING(telenumber,8,1)!=SUBSTRING(telenumber,11,1);
查询结果:
telenumber
------------
130****1112
189****2223
189****3334
第四种类型: ABBB
Sql代码
select * from telephone where SUBSTRING(telenumber,9,1)=SUBSTRING(telenumber,10,1) and SUBSTRING(telenumber,11,1)=SUBSTRING(telenumber,10,1) and SUBSTRING(telenumber,8,1)!=SUBSTRING(telenumber,11,1);
查询结果:
telenumber
------------
189****7888
189****6777
189****5666
第五种类型: AAAA
Sql代码
select * from telephone where SUBSTRING(telenumber,8,2)=SUBSTRING(telenumber,10,2) and SUBSTRING(telenumber,8,1)=SUBSTRING(telenumber,11,1);
查询结果:
telenumber
------------
130****1111
130****8888
130****9999