查找数字重复次数是否多于4次- ORACLE SQL

Question

我有一个电话号码列表(在ORACLE SQL中)，如果一个数字连续包含4倍的数字，我必须检查这个列表。在这种情况下，我必须将值设置为null。例:

<表类> 电话 状态 tbody><<tr>012345678对021444458空339547214对111105874空

我是这样理解的(阅读代码中的注释):

SQL> with test (tel) as
2    -- sample data
3    (select '012345678' from dual union all
4     select '021444458' from dual union all
5     select '339547214' from dual union all
6     select '111105874' from dual
7    ),

---

8  cons (dig) as
9    -- generated consecutive digits, 0000, 1111, ..., 9999
10    (select lpad(level - 1, 4, level - 1)
11     from dual
12     connect by level <= 10
13    ),
14  temp as
15    -- check whether any TEL number constains consecutive digits
16    (select t.tel,
17       c.dig,
18       case when instr(t.tel, c.dig) > 0 then 'NOK'
19            else 'OK'
20       end as status
21     from test t cross join cons c
22    )
23  -- finally, two groups: those that are OK (don't contain consecutive digits) and ...
24  select a.tel, 'OK'
25  from test a
26  where not exists (select null from temp b
27                    where b.tel = a.tel
28                      and b.status = 'NOK'
29                   )
30  union all
31  -- ... those that contain 4 consecutive digits
32  select a.tel, 'NULL'
33  from test a
34  where     exists (select null from temp b
35                    where b.tel = a.tel
36                      and b.status = 'NOK'
37                   )
38  order by tel;

---

TEL       'OK'
--------- ----
012345678 OK
021444458 NULL
111105874 NULL
339547214 OK
SQL>

---

[EDIT: converting to UPDATE]

数据之前:

SQL> SELECT * FROM test;
TEL       STATUS
--------- ----------
012345678
021444458
339547214
111105874

更新:

SQL> UPDATE test t
2     SET t.status =
3            (WITH
4                cons (dig)
5                AS
6                   -- generated consecutive digits, 0000, 1111, ..., 9999
7                   (    SELECT LPAD (LEVEL - 1, 4, LEVEL - 1)
8                          FROM DUAL
9                    CONNECT BY LEVEL <= 10),
10                temp
11                AS
12                   -- check whether any TEL number constains consecutive digits
13                   (SELECT t.tel,
14                           c.dig,
15                           CASE
16                              WHEN INSTR (t.tel, c.dig) > 0 THEN 'NOK'
17                              ELSE 'OK'
18                           END AS status
19                      FROM test t CROSS JOIN cons c),
20                -- finally, two groups: those that are OK (don't contain consecutive digits) and ...
21                temp2
22                AS
23                   (SELECT a.tel, 'OK' status
24                      FROM test a
25                     WHERE NOT EXISTS
26                              (SELECT NULL
27                                 FROM temp b
28                                WHERE     b.tel = a.tel
29                                      AND b.status = 'NOK')
30                    UNION ALL
31                    -- ... those that contain 4 consecutive digits
32                    SELECT a.tel, 'NULL' status
33                      FROM test a
34                     WHERE EXISTS
35                              (SELECT NULL
36                                 FROM temp b
37                                WHERE     b.tel = a.tel
38                                      AND b.status = 'NOK'))
39             SELECT t2.status
40               FROM temp2 t2
41              WHERE t2.tel = t.tel);
4 rows updated.

数据后:

SQL> SELECT * FROM test;
TEL       STATUS
--------- ----------
012345678 OK
021444458 NULL
339547214 OK
111105874 NULL
SQL>

作为另一种解决方案，您可以使用regexp_like来完成这项工作。正则表达式(d)(1){3，}将匹配连续重复至少四次的任何数字。

with t (TEL) as (
select '012345678' from dual union all
select '021444458' from dual union all
select '339547214' from dual union all
select '111105874' from dual
)
select TEL, case when not regexp_like(TEL, '(d)(1){3,}') then 'OK' end STATUS
from t ;

那么，update就像这样

UPDATE test t
SET t.status = case when not regexp_like(TEL, '(d)(1){3,}') then 'OK' else null end
;