在 Oracle 中使用正则表达式

转自：http://www.cnblogs.com/killkill/archive/2010/09/06/1819536.html

Oracle使用正则表达式离不开这4个函数：

1。regexp_like

2。regexp_substr

3。regexp_instr

4。regexp_replace

看函数名称大概就能猜到有什么用了。

 

regexp_like 只能用于条件表达式，和 like 类似，但是使用的正则表达式进行匹配，语法很简单：

regexp_substr 函数，和 substr 类似，用于拾取合符正则表达式描述的字符子串，语法如下：

regexp_instr 函数，和 instr 类似，用于标定符合正则表达式的字符子串的开始位置，语法如下：

regexp_replace 函数，和 replace 类似，用于替换符合正则表达式的字符串，语法如下：

这里解析一下几个参数的含义：

1。source_char，输入的字符串，可以是列名或者字符串常量、变量。

2。pattern，正则表达式。

3。match_parameter，匹配选项。

        取值范围： i：大小写不敏感； c：大小写敏感；n：点号 . 不匹配换行符号；m：多行模式；x：扩展模式，忽略正则表达式中的空白字符。

4。position，标识从第几个字符开始正则表达式匹配。

5。occurrence，标识第几个匹配组。

6。replace_string，替换的字符串。

 

说了一堆文绉绉的，现在开始实例演练了，在此之前先建好一个表。

view sourceprint?

01create table tmp as 

02with data as (

03  select'like' as id ,'a9999' asstr from dual union all

04  select'like'       ,'a9c'         from dual unionall

05  select'like'       ,'A7007'       from dual unionall

06  select'like'       ,'123a34cc'    from dual unionall 

07  select'substr'     ,'123,234,345' from dual unionall

08  select'substr'     ,'12,34.56:78' from dual unionall

09  select'substr'     ,'123456789'   from dual unionall

10  select'instr'      ,'192.168.0.1' from dual unionall

11  select'replace'    ,'(020)12345678'from dual unionall

12  select'replace'    ,'001517729C28'from dual  

13)

14select * from data ;

15  

16select * from tmp ;

17ID      STR

18------- -------------

19like    a9999

20like    a9c

21like    A7007

22like    123a34cc

23substr  123,234,345

24substr  12,34.56:78

25substr  123456789

26instr   192.168.0.1

27replace (020)12345678

28replace 001517729C28

regexp_like 例子：

view sourceprint?

01select str from tmp where id='like'and regexp_like(str,'A\d+','i');-- 'i' 忽略大小写

02STR

03-------------

04a9999

05a9c

06A7007

07123a34cc

08  

09select str from tmp where id='like'and regexp_like(str, 'a\d+');

10STR

11-------------

12a9999

13a9c

14123a34cc

15  

16select str from tmp where id='like'and regexp_like(str,'^a\d+');

17STR

18-------------

19a9999

20a9c

21  

22select str from tmp where id='like'and regexp_like(str,'^a\d+$');

23STR

24-------------

25a9999

regexp_substr 例子：

view sourceprint?

01col str format a15;

02select 

03  str,

04  regexp_substr(str,'[^,]+')     str,

05  regexp_substr(str,'[^,]+',1,1) str,

06  regexp_substr(str,'[^,]+',1,2) str, -- occurrence 第几个匹配组

07  regexp_substr(str,'[^,]+',2,1) str  -- position 从第几个字符开始匹配

08from tmp

09where id='substr';

10STR             STR             STR             STR             STR

11--------------- --------------- --------------- --------------- ---------------

12123,234,345     123             123             234             23

1312,34.56:78     12              12              34.56:78        2

14123456789       123456789       123456789                       23456789

15  

16select 

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 

22from tmp      

23where id='substr';

24STR             STR             STR             STR             STR

25--------------- --------------- --------------- --------------- ---------------

26123,234,345     1               123             23              234

2712,34.56:78     1               12              34

28123456789       1               123456789       34              234

29  

30  

31select regexp_substr('123456789','\d',1,level) str --取出每位数字，有时这也是行转列的方式

32from dual

33connect by level<=9

34STR

35---------------

361

372

383

394

405

416

427

438

449

regex_instr 例子：

view sourceprint?

01col ind format 9999;

02select

03  str, 

04  regexp_instr(str,'\.'   ) ind ,

05  regexp_instr(str,'\.',1,2) ind ,

06  regexp_instr(str,'\.',5,2) ind

07from tmp where id='instr';

08STR               IND   IND   IND

09--------------- ----- ----- -----

10192.168.0.1         4     8    10

11      

12select 

13  regexp_instr('192.168.0.1','\.',1,level) ind ,  -- 点号. 所在的位置

14  regexp_instr('192.168.0.1','\d',1,level) ind    -- 每个数字的位置

15from dual 

16connect 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 例子：

view sourceprint?

01select 

02  str,

03  regexp_replace(str,'020','GZ') str,

04  regexp_replace(str,'(\d{3})(\d{3})','<\2\1>') str-- 将第一、第二捕获组交换位置，用尖括号标识出来

05from tmp

06where id='replace';  

07STR             STR             STR

08--------------- --------------- ---------------

09(020)12345678   (GZ)12345678    (020)<456123>78

10001517729C28    001517729C28    <517001>729C28

综合应用的例子：

view sourceprint?

01col row_line format a30;

02with sudoku as (

03  select'020000080568179234090000010030040050040205090070080040050000060289634175010000020'as line

04  fromdual

05),

06tmp as(

07  selectregexp_substr(line,'\d{9}',1,level) row_line,

08  levelcol

09  fromsudoku

10  connectby level<=9

11)

12select regexp_replace( row_line ,'(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)','\1 \2 \3 \4 \5 \6 \7 \8 \9') row_line

13from tmp

14  

15ROW_LINE

16------------------------------

170 2 0 0 0 0 0 8 0

185 6 8 1 7 9 2 3 4

190 9 0 0 0 0 0 1 0

200 3 0 0 4 0 0 5 0

210 4 0 2 0 5 0 9 0

220 7 0 0 8 0 0 4 0

230 5 0 0 0 0 0 6 0

242 8 9 6 3 4 1 7 5

250 1 0 0 0 0 0 2 0