mysql手机号码规律查询

news/2024/11/16 6:22:45/

tips：所有手机号均为随机创建的，为了做查询的效果展示，不涉及他人隐私。

mysql手机号码规律查询

  • tips：所有手机号均为随机创建的，为了做查询的效果展示，不涉及他人隐私。
  • 数据库
  • 全号规律
    • AAA
    • ABAB
    • AABBCC
    • ABCD(顺子，1234或4321)
    • AAABBB
    • 6多8多9多(举例:11位的号码中存在超过6个)
    • ABBCCDDA(更新时间:2021/04/21)
  • 中间规律
    • AAA
    • ABAB
    • AABBCC
    • ABCD(顺子，1234或4321)
    • AAABBB
  • 尾号规律
    • AAA
    • ABAB
    • AABBCC
    • ABCD(顺子，1234或4321)
    • AAABBB

数据库

数据库中的表只使用了一个字段：phone_number
在这里插入图片描述

全号规律

AAA

select phone_number from `phone_number` where 
(
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
)
OR
(
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
)
OR
(
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
)
OR
(
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
OR
(
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
OR
(
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
OR
(
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)
OR
(
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

ABAB

select phone_number from `phone_number` where 
(
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,2,1)
and
SUBSTR(phone_number,1,1) = SUBSTR(phone_number,3,1)
AND
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
)
or
(
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
)
or
(
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
)
or
(
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
or
(
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
or
(
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
or
(
SUBSTR(phone_number,7,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)
or
(
SUBSTR(phone_number,8,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
AND
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

AABBCC

select phone_number from `phone_number` where 
(
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,1,1) = SUBSTR(phone_number,2,1)
AND
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
)
or
(
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
AND
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
)
or
(
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
)
or
(
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
)
or
(
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,7,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
AND
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
)
or
(
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,8,1) != SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
AND
SUBSTR(phone_number,10,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

ABCD(顺子，1234或4321)

select phone_number from `phone_number` where 
((SUBSTR(phone_number,1,1) = (SUBSTR(phone_number,2,1)+1)andSUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)+1)andSUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1))OR(SUBSTR(phone_number,1,1) = (SUBSTR(phone_number,2,1)-1)andSUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)-1)andSUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1))
)
OR
((SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)+1)andSUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1)andSUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1))or(SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)-1)andSUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1)andSUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1))
)
OR
((SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1)andSUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1)andSUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1))OR(SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1)andSUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1)andSUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1))
)
OR
((SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1)andSUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1)ANDSUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1))OR(SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1)andSUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1)ANDSUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1))
)
OR
((SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1)andSUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1)andSUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1))OR(SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1)andSUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1)andSUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1))
)
OR
((SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1)andSUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1)andSUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1))OR(SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1)andSUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1)andSUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1))
)
OR
((SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1)andSUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1)andSUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)+1))OR(SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1)andSUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1)andSUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)-1))
)
OR
((SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1)andSUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)+1)ANDSUBSTR(phone_number,10,1) = (SUBSTR(phone_number,11,1)+1))OR(SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1)andSUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)-1)ANDSUBSTR(phone_number,10,1) = (SUBSTR(phone_number,11,1)-1))
)

在这里插入图片描述

AAABBB

select phone_number from `phone_number` where
(
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
OR
(
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
OR
(
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
OR
(
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)
OR
(
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

6多8多9多(举例:11位的号码中存在超过6个)

tips:这里采用了一种比较巧妙的方式. 将存在6的号码替换为空,将11位减去替换后的长度就是6出现的个数.以此类推,将6的个数+8的个数+9的个数

select phone_number from `phone_number` where
((LENGTH(phone_number) - LENGTH(REPLACE(phone_number,"6",""))) +(LENGTH(phone_number) - LENGTH(REPLACE(phone_number,"8",""))) +(LENGTH(phone_number) - LENGTH(REPLACE(phone_number,"9",""))) 
)>6

在这里插入图片描述

ABBCCDDA(更新时间:2021/04/21)

select * from `phone_number` where  
(																											
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,2,1)
and
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,1,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
)
or
(
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
)
or
(
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
)
or
(
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,11,1)
and
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,7,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
)

ABBCCDDA

中间规律

AAA

select phone_number from `phone_number` where
(
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
)
OR
(
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
)
OR
(
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
)
OR
(
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
OR
(
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
OR
(
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
OR
(
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)

在这里插入图片描述

ABAB

select phone_number from `phone_number` where
(
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,2,1)
and
SUBSTR(phone_number,1,1) = SUBSTR(phone_number,3,1)
AND
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
)
or
(
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
)
or
(
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
)
or
(
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
or
(
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
or
(
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
or
(
SUBSTR(phone_number,7,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)

在这里插入图片描述

AABBCC

select phone_number from `phone_number` where
(
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,1,1) = SUBSTR(phone_number,2,1)
AND
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
)
or
(
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
AND
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
)
or
(
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
)
or
(
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
)
or
(
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,7,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
AND
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
)

在这里插入图片描述

ABCD(顺子，1234或4321)

select phone_number from `phone_number` where
((SUBSTR(phone_number,1,1) = (SUBSTR(phone_number,2,1)+1)andSUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)+1)andSUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1))OR(SUBSTR(phone_number,1,1) = (SUBSTR(phone_number,2,1)-1)andSUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)-1)andSUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1))
)
OR
((SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)+1)andSUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1)andSUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1))or(SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)-1)andSUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1)andSUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1))
)
OR
((SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1)andSUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1)andSUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1))OR(SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1)andSUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1)andSUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1))
)
OR
((SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1)andSUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1)ANDSUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1))OR(SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1)andSUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1)ANDSUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1))
)
OR
((SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1)andSUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1)andSUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1))OR(SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1)andSUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1)andSUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1))
)
OR
((SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1)andSUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1)andSUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1))OR(SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1)andSUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1)andSUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1))
)
OR
((SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1)andSUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1)andSUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)+1))OR(SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1)andSUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1)andSUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)-1))
)

在这里插入图片描述

AAABBB

select phone_number from `phone_number` where
(
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
OR
(
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
OR
(
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
OR
(
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)

在这里插入图片描述

尾号规律

AAA

select phone_number from `phone_number` where
(
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

ABAB

select phone_number from `phone_number` where
(
SUBSTR(phone_number,8,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
AND
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

AABBCC

select phone_number from `phone_number` where
(
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,8,1) != SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
AND
SUBSTR(phone_number,10,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

ABCD(顺子，1234或4321)

select phone_number from `phone_number` where
((SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1)andSUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)+1)ANDSUBSTR(phone_number,10,1) = (SUBSTR(phone_number,11,1)+1))OR(SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1)andSUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)-1)ANDSUBSTR(phone_number,10,1) = (SUBSTR(phone_number,11,1)-1))
)

在这里插入图片描述

AAABBB

select phone_number from `phone_number` where
(
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述


http://www.ppmy.cn/news/724906.html

相关文章

批量查询手机号码运营商信息

批量查询手机号码运营商信息

项目场景： 批量查询手机号码信息 问题描述： 有同事需要批量查询手机号码的运营商，归属地区号信息 思路： 百度搜索，爬虫取数，学的不精暂时还不会直接放弃python 自带一个phone库，缺点是查不到…
阅读更多...
知识点速记 | 本机号码一键登录？

知识点速记 | 本机号码一键登录？

目录 中国移动 中国电信 很多 APP 的目前都支持「本机号码一键登录」功能。本机号码一键登录是基于运营商独有网关认证能力推出的账号认证产品。用户只需一键授权，即可实现以本机号码注册/登录，相比先前的短信验证码流程体验更优。 目前市面上有很多厂…
阅读更多...
手机号码归属地查询 - 一刀工具

手机号码归属地查询 - 一刀工具

手机号码归属地快速查询移动，联通，电信手机号码归属地，免费手机号码运营商查询，手机归属地查询大全。 代码片段 //获取手机信息static function getMobile($mobile){try {$phone new PhoneHelper();$res $phone->setPhone($m…
阅读更多...
JSP网上手机商城系统 用eclipse定制开发mysql数据库BS模式java编程jdbc

JSP网上手机商城系统 用eclipse定制开发mysql数据库BS模式java编程jdbc

一、源码特点 JSP 网上手机商城系统是一套完善的web设计系统，对理解JSP java SERLVET mvc编程开发语言有帮助，系统具有完整的源代码和数据库，系统主要采用B/S模式开发。开发环境为 TOMCAT7.0,eclipse开发，数据库为Mysql5.0&a…
阅读更多...
如何快速查询手机号码归属地和运营商

如何快速查询手机号码归属地和运营商

最近公司项目需要做一个快速查询手机号码归属地和运营商的小功能，想着如果用现成的API就可以大大提高开发效率，所以在网上的API商店搜索了一番，发现了 APISpace，它里面的手机号码归属地和运营商查询API非常符合我的开发需求。 手…
阅读更多...
手机号码信息查询器

手机号码信息查询器

发送SOAP数据给服务器调用webservice，实现手机号归属地查询
阅读更多...
car music

car music

正常车载U盘即可 车载不可用的U盘 车载需要改进遍历文件夹
阅读更多...
Android网络安全配置network_security_config

Android网络安全配置network_security_config

Android开发过程中，如果出现网络请求错误，测试经常会抓包来查看请求的情况。 在Android6.0 及以下系统可以抓包，而 Android7.0 及以上系统不能再抓包了，因为Android7.0及以上系统版本新增了证书验证，所以 app 内不再像…
阅读更多...
最新文章

Copyright @ 2022~2023