oracle正则提取,oracle正则表达式，提取连续4个及以上的数字。

SQL> with t as

2 (select 13714430798 num

3 from dual

4 union all

5 select 13714432105

6 from dual

7 union all

8 select 13714412345

9 from dual

10 union all

11 select 13714456784

12 from dual

13 union all

14 select 13714498769

15 from dual

16 union all

17 select 13712341234 from dual),

18 tmp as

19 (select a.num, b.rn, to_number(substr(a.num, b.rn, 1)) i

20 from t a, (select rownum rn from dual connect by rownum <= 11) b)

21 select x.num, to_char(y.str) as str

22 from t x,

23 (select num, wm_concat(replace(str, ',')) str

24 from (select num, max(sys_connect_by_path(i, ',')) str

25 from tmp x

26 where level >= 4

27 start with not exists (select 1

28 from tmp

29 where num = x.num

30 and rn = x.rn - 1

31 and i = x.i - 1)

32 connect by prior num = num

33 and prior rn = rn - 1

34 and prior i = i - 1

35 group by num, connect_by_root rn

36 union all

37 select num, max(sys_connect_by_path(i, ',')) str

38 from tmp x

39 where level >= 4

40 start with not exists (select 1

41 from tmp

42 where num = x.num

43 and rn = x.rn - 1

44 and i = x.i + 1)

45 connect by prior num = num

46 and prior rn = rn - 1

47 and prior i = i + 1

48 group by num, connect_by_root rn)

49 group by num) y

50 where x.num = y.num(+);

NUM STR

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

1371234123 1234,1234

1371441234 12345

1371443210 43210

1371445678 45678

1371449876 9876

1371443079

6 rows selected