Oracle使用正則表達(dá)式離不開這4個(gè)函數(shù)：
1。regexp_like
2。regexp_substr
3。regexp_instr
4。regexp_replace
看函數(shù)名稱大概就能猜到有什么用了。
regexp_like 只能用于條件表達(dá)式，和 like 類似，但是使用的正則表達(dá)式進(jìn)行匹配，語法很簡單：
regexp_substr 函數(shù)，和 substr 類似，用于拾取合符正則表達(dá)式描述的字符子串，語法如下：
regexp_instr 函數(shù)，和 instr 類似，用于標(biāo)定符合正則表達(dá)式的字符子串的開始位置，語法如下：
regexp_replace 函數(shù)，和 replace 類似，用于替換符合正則表達(dá)式的字符串，語法如下：
這里解析一下幾個(gè)參數(shù)的含義：
1。source_char，輸入的字符串，可以是列名或者字符串常量、變量。
2。pattern，正則表達(dá)式。
3。match_parameter，匹配選項(xiàng)。
取值范圍： i：大小寫不敏感； c：大小寫敏感；n：點(diǎn)號(hào) . 不匹配換行符號(hào)；m：多行模式；x：擴(kuò)展模式，忽略正則表達(dá)式中的空白字符。
4。position，標(biāo)識(shí)從第幾個(gè)字符開始正則表達(dá)式匹配。
5。occurrence，標(biāo)識(shí)第幾個(gè)匹配組。
6。replace_string，替換的字符串。
說了一堆文縐縐的，現(xiàn)在開始實(shí)例演練了，在此之前先建好一個(gè)表。
01 create table tmp as
02 with data as (
03 select 'like' as id ,'a9999' as str from dual union all
04 select 'like' ,'a9c' from dual union all
05 select 'like' ,'A7007' from dual union all
06 select 'like' ,'123a34cc' from dual union all
07 select 'substr' ,'123,234,345' from dual union all
08 select 'substr' ,'12,34.56:78' from dual union all
09 select 'substr' ,'123456789' from dual union all
10 select 'instr' ,'192.168.0.1' from dual union all
11 select 'replace' ,'(020)12345678' from dual union all
12 select 'replace' ,'001517729C28' from dual
13 )
14 select * from data ;
15
16 select * from tmp ;
17 ID STR
18 ------- -------------
19 like a9999
20 like a9c
21 like A7007
22 like 123a34cc
23 substr 123,234,345
24 substr 12,34.56:78
25 substr 123456789
26 instr 192.168.0.1
27 replace (020)12345678
28 replace 001517729C28
regexp_like 例子：
01 select str from tmp where id='like' and regexp_like(str,'A\d+','i'); -- 'i' 忽略大小寫
02 STR
03 -------------
04 a9999
05 a9c
06 A7007
07 123a3
4cc
08
09 select str from tmp where id='like' and regexp_like(str, 'a\d+');
10 STR
11 -------------
12 a9999
13 a9c
14 123a34cc
15
16 select str from tmp where id='like' and regexp_like(str,'^a\d+');
17 STR
18 -------------
19 a9999
20 a9c
21
22 select str from tmp where id='like' and regexp_like(str,'^a\d+$');
23 STR
24 -------------
25 a9999
regexp_substr 例子：
01 col str format a15;
02 select
03 str,
04 regexp_substr(str,'[^,]+') str,
05 regexp_substr(str,'[^,]+',1,1) str,
06 regexp_substr(str,'[^,]+',1,2) str, -- occurrence 第幾個(gè)匹配組
07 regexp_substr(str,'[^,]+',2,1) str -- position 從第幾個(gè)字符開始匹配
08 from tmp
09 where id='substr';
10 STR STR STR STR STR
11 --------------- --------------- --------------- --------------- ---------------
12 123,234,345 123 123 234 23
13 12,34.56:78 12 12 34.56:78 2
14 123456789 123456789 123456789 23456789
15
16 select
17 str,
18 regexp_substr(str,'\d') str,
19 regexp_substr(str,'\d+' ,1,1) str,
20 regexp_substr(str,'\d{2}',1,2) str,
21 regexp_substr(str,'\d{3}',2,1) str
22 from tmp
23 where id='substr';
24 STR STR STR STR STR
25 --------------- --------------- --------------- --------------- ---------------
26 123,234,345 1 123 23 234
27 12,34.56:78 1 12 34
28 123456789 1 123456789 34 234
29
30
31 select regexp_substr('123456789','\d',1,level) str --取出每位數(shù)字，有時(shí)這也是行轉(zhuǎn)列的方式
32 from dual
33 connect by level<=9
34 STR
35 ---------------
36 1
37 2
38 3
39 4
40 5
41 6
42 7
43 8
44 9
regex_instr 例子：
01 col ind format 9999;
02 select
03 str,
04 regexp_instr(str,'\.' ) ind ,
05 regexp_instr(str,'\.',1,2) ind ,
06 regexp_instr(str,'\.',5,2) ind
07 from tmp where id='instr';
08 STR IND IND IND
09 --------------- ----- ----- -----
10 192.168.0.1 4 8 10
11
12 select
13 regexp_instr('192.168.0.1','\.',1,level) ind , -- 點(diǎn)號(hào). 所在的位置
14 regexp_instr('192.168.0.1','\d',1,level) ind -- 每個(gè)數(shù)字的位置
15 from dual
16 connect by level <= 9
17 IND IND
18 ----- -----
19 4 1
20 8 2
21 10 3
22 0 5
23 0 6
24 0 7
25 0 9
26 0 11
27 0 0
regex_replace 例子：
01 select
02 str,
03 regexp_replace(str,'020','GZ') str,
04 regexp_replace(str,'(\d{3})(\d{3})','<\2\1>') str -- 將第一、第二捕獲組交換位置，用尖括號(hào)標(biāo)識(shí)出來
05 from tmp
06 where id='replace';
07 STR STR STR
08 --------------- --------------- ---------------
09 (020)12345678 (GZ)12345678 (020)<456123>78
10 001517729C28 001517729C28 <517001>729C28
綜合應(yīng)用的例子：
01 col row_line format a30;
02 with sudoku as (
03 select '020000080568179234090000010030040050040205090070080040050000060289634175010000020' as line
04 from dual
05 ),
06 tmp as (
07 select regexp_substr(line,'\d{9}',1,level) row_line,
08 level col
09 from sudoku
10 connect by level<=9
11 )
12 select regexp_replace( row_line ,'(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)','\1 \2 \3 \4 \5 \6 \7 \8 \9') row_line
13 from tmp
14
15 ROW_LINE
16 ------------------------------
17 0 2 0 0 0 0 0 8 0
18 5 6 8 1 7 9 2 3 4
19 0 9 0 0 0 0 0 1 0
20 0 3 0 0 4 0 0 5 0
21 0 4 0 2 0 5 0 9 0
22 0 7 0 0 8 0 0 4 0
23 0 5 0 0 0 0 0 6 0
24 2 8 9 6 3 4 1 7 5
25 0 1 0 0 0 0 0 2 0