# 连续相同号码的识别

13677884444
13988400965
13973250074
13974416087
13999750304
13967311183
13999806632

1、数字拼接法。即首先生成若干数字，然后按照一定序号通过字符串拼接函数拼接大于该序号的字符串即可。
4、半正则法。即对0-9每个数字做{3,8}匹配。

with
tmp as(select '139' || trunc(dbms_random.value(100000000, 1000000000)) aa
from dual connect by rownum <= 10000),
ttt as (select replace(ssum(c1), ',') c1, replace(ssum(c2), ',') c2,
replace(ssum(c3), ',') c3, replace(ssum(c4), ',') c4,
replace(ssum(c5), ',') c5, replace(ssum(c6), ',') c6
from (select a.rn,
decode(sign(row_number()over(partition by a.rn order by rownum)-4), -1, a.rn) c1,
decode(sign(row_number()over(partition by a.rn order by rownum)-5), -1, a.rn) c2,
decode(sign(row_number()over(partition by a.rn order by rownum)-6), -1, a.rn) c3,
decode(sign(row_number()over(partition by a.rn order by rownum)-7), -1, a.rn) c4,
decode(sign(row_number()over(partition by a.rn order by rownum)-8), -1, a.rn) c5,
decode(sign(row_number()over(partition by a.rn order by rownum)-9), -1, a.rn) c6
from (select rownum - 1 rn from dual connect by rownum <= 10) a,
(select rownum rn from dual connect by rownum <= 8) b)
group by rn)
select * from tmp t, ttt x
where instr(t.aa, x.c1) > 0
or instr(t.aa, x.c2) > 0
or instr(t.aa, x.c3) > 0
or instr(t.aa, x.c4) > 0
or instr(t.aa, x.c5) > 0
or instr(t.aa, x.c6) > 0

SQL> select lpad(rownum-1, 8, rownum-1) from dual connect by rownum <= 10;

-------------------------
00000000
11111111
22222222
33333333
44444444
55555555
66666666
77777777
88888888
99999999

10 rows selected

SQL> select substr(a.n, 1, r) nn
2    from (select lpad(rownum-1, 8, rownum-1) n from dual connect by rownum <= 10) a,
3         (select rownum + 2 r from dual connect by rownum <= 6) b
4  /

NN
--------------------------------
000
0000
00000
000000
0000000
00000000
111
1111
11111
111111
1111111
11111111
222
2222
22222
222222
2222222
22222222
333
3333
33333
333333
3333333
33333333
444
4444
44444
444444
4444444
44444444
555
5555
55555
555555
5555555
55555555
666
6666
66666
666666
6666666
66666666
777
7777
77777
777777
7777777
77777777
888
8888
88888
888888
8888888
88888888
999
9999
99999
999999
9999999
99999999

60 rows selected
ok，大功告成，接下去就是匹配了：
SQL> with
2  tmp as(select '139' || trunc(dbms_random.value(100000000, 1000000000)) aa
3           from dual connect by rownum <= 10000),
4  ttt as (select substr(a.n, 1, r) l
5    from (select lpad(rownum-1, 8, rownum-1) n from dual connect by rownum <= 10) a,
6         (select rownum + 2 r from dual connect by rownum <= 6) b)
7  select p.aa from tmp p, ttt t
8   where instr(p.aa, t.l) >= 1
9  /

AA
-------------------------------------------
139628435559
139111449653
139927388845
139323055525
139947487666
139777541560
.....
100条在0.4s左右。

with
tmp as(select '139' || trunc(dbms_random.value(100000000, 1000000000)) aa
from dual connect by rownum <= 10000),
ttt as (select rpad(lv, row_number()over(partition by lv order by lv)+2, lv) l
from (select ceil(level/6)-1 lv
from dual
connect by level <= 60))
select p.aa from tmp p, ttt t
where instr(p.aa, t.l) >= 1

with
tmp as(select '139' || trunc(dbms_random.value(100000000, 1000000000)) aa
from dual connect by rownum <= 10000),
ttt as (select rownum - 1 r from dual connect by rownum <= 10)
select p.aa from tmp p, ttt t
where regexp_like(p.aa, t.r||'{3,8}')

• 博文量
257
• 访问量
1048658