有一张表t_card,里面有一个号码的字段tel_no,如何通过正则表达式查询除前3位外后面8位符合以下规则的号码:
1.正连ABCDE(如:12345)
2.倒连EDCBA(如:54321)
3.ABAB(如:1212)
4.ABBA(如:1221)
5.AAAA(如:1111)
6.AAA(如:111)
7.AABB(如:1122)
等等,希望能直接给出sql,网上一些正则试过了不行,多数都是代码里面匹配的,希望各位多多指点啊
1.正连ABCDE(如:12345)
2.倒连EDCBA(如:54321)
3.ABAB(如:1212)
4.ABBA(如:1221)
5.AAAA(如:1111)
6.AAA(如:111)
7.AABB(如:1122)
等等,希望能直接给出sql,网上一些正则试过了不行,多数都是代码里面匹配的,希望各位多多指点啊
其他规则都类似,谢谢了,就类似移动、联通、电信选号码一样
with tab as(
select '13600001234' telphone from dual union all
select '13600001548' from dual union all
select '13600001544' from dual union all
select '13600001148' from dual union all
select '13600002345' from dual union all
select '13600003148' from dual union all
select '13500004321' from dual union all
select '13500003333' from dual union all
select '13500001148' from dual union all
select '13500006543' from dual union all
select '13500004567' from dual union all
select '13400000123' from dual union all
select '13400001111' from dual union all
select '13400007567' from dual
)
--四位递增或四位递减
select telphone from tab
where
(substr(telphone, 8, 1) = substr(telphone, 9, 1) - 1
and
substr(telphone, 9, 1) = substr(telphone, 10, 1) - 1
and
substr(telphone, 10, 1)= substr(telphone, 11, 1) - 1)
or
(substr(telphone, 8, 1) = substr(telphone, 9, 1) + 1
and
substr(telphone, 9, 1) = substr(telphone, 10, 1) + 1
and
substr(telphone, 10, 1)= substr(telphone, 11, 1) + 1)
--四连号
select telphone from tab
where regexp_like(telphone,'([[:digit:]])\1{3}$')
--or where regexp_like(telphone,'(0{4}$)|(1{4}$)|(2{4}$)|(3{4}$)|(4{4}$)|(5{4}$)|(6{4}$)|(7{4}$)|(8{4}$)|(9{4}$)')
顺便问下那种ABAB或者AABB和ABBA的如何匹配?