1.ORACLE正则表达式函数
1.1 REGEXP_LIKE
1
2
|
select SXZYY from SW_ZH_ZHMX where REGEXP_LIKE(SXZYY, '^wd', 'i');
|
SXZYY
21321
1232
11
111
11
12
12312
231231
121
23
11111
2132132112
q1231
211111111111
2132131
2312321
111
23213
23213
10
11
1010
11
10
10
10
101
11
10
102
10
101
10
10
10
10
10
200
100
1000
123
231
1231
1231
1231
1.2 REGEXP_INSTR
1
|
select SXZYY, REGEXP_INSTR(SXZYY, '[3]'), REGEXP_INSTR(SXZYY, '[q]') from SW_ZH_ZHMX where REGEXP_LIKE(SXZYY, '^wd');
|
SXZYY REGEXP_INSTR(SXZYY,’[3]’) REGEXP_INSTR(SXZYY,’[q]’)
21321 3 0
1232 3 0
11 0 0
111 0 0
11 0 0
12 0 0
12312 3 0
231231 2 0
121 0 0
23 2 0
11111 0 0
2132132112 3 0
q1231 4 1
211111111111 0 0
2132131 3 0
2312321 2 0
111 0 0
23213 2 0
23213 2 0
10 0 0
11 0 0
1010 0 0
11 0 0
10 0 0
10 0 0
10 0 0
101 0 0
11 0 0
10 0 0
102 0 0
10 0 0
101 0 0
10 0 0
10 0 0
10 0 0
10 0 0
10 0 0
200 0 0
100 0 0
1000 0 0
123 3 0
231 2 0
1231 3 0
1231 3 0
1231 3 0
1.3 REGEXP_REPLACE
1
|
select SXZYY, REGEXP_REPLACE(SXZYY, '[1]', 'L') from SW_ZH_ZHMX where REGEXP_LIKE(SXZYY, '^wd');
|
1.4 REGEXP_SUBSTR
1
2
3
|
select
REGEXP_SUBSTR('01 = 1:02 = 2:03 = 3:04 = 4', '[^:]+', 1, rownum) as newport,rownum as hrow
from dual connect by rownum <= REGEXP_COUNT('01 = 1:02 = 2:03 = 3:04 = 4', '[^:]+')
|
1.5 REGEXP_COUNT
REGEXP_COUNT函数可以取到有多少匹配的个数
1
2
3
|
select
REGEXP_COUNT('01 = 1:02 = 2:03 = 3:04 = 4', '[^:]+')
from dual
|
1.6 REGEXP_SUBSTR和REGEXP_COUNT组合使用
1
2
3
|
select
REGEXP_SUBSTR('01 = 1:02 = 2:03 = 3:04 = 4', '[^:]+', 1, rownum) as newport,rownum as hrow
from dual connect by rownum <= REGEXP_COUNT('01 = 1:02 = 2:03 = 3:04 = 4', '[^:]+')
|
近期评论