Question

用oracle正则表达式1.号码尾数后4位含3个以上（含3个）相同号码----如AAAA、BAAA、AAAA,注A不等于

Answer 1

递增的用正则表达式处理很麻烦. 我就只列几个简单的吧.

CREATE TABLE test_phone (

phone_numbervarchar(20)

);

INSERT INTO test_phone

SELECT '13812345678' FROMdualUNION ALL

SELECT '13812345566' FROMdualUNION ALL

SELECT '13812345656' FROMdualUNION ALL

SELECT '13812345556' FROMdualUNION ALL

SELECT '13812346666' FROMdualUNION ALL

SELECT '13855661234' FROMdualUNION ALL

SELECT '13856561234' FROMdualUNION ALL

SELECT '13855561234' FROMdualUNION ALL

SELECT '13866661234' FROMdual;

--AAAA 结尾的

SELECT

*

FROM

test_phone

WHERE

REGEXP_LIKE(REVERSE(phone_number), '^(d)111');

PHONE_NUMBER

----------------------------------------

13812346666

--AABB 结尾的

SELECT

*

FROM

test_phone

WHERE

REGEXP_LIKE(REVERSE(phone_number), '^(d)1([^1])2')

AND NOT REGEXP_LIKE(REVERSE(phone_number), '^(d)111');

PHONE_NUMBER

----------------------------------------

13812345656

--ABAB 结尾的

SELECT

*

FROM

test_phone

WHERE

REGEXP_LIKE(REVERSE(phone_number), '^(d)(d)12')

AND NOT REGEXP_LIKE(REVERSE(phone_number), '^(d)111');

PHONE_NUMBER

----------------------------------------

13812345656