oracle的正则表达式

本文摘要：
10g正则表达式提高了SQL灵活性。
无关的空白检测，或者分解多个正则组成的字符串等问题。
10g支持正则表达式的四个新函数分别是：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和REGEXP_REPLACE。
正则表达式代替了老的百分号（%）和通配符（_）字符。
匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
匹配前面的子表达式零次或一次。
目前，正则表达式已经在很多软件中得到广泛的应用，包括*nix（Linux, Unix等），HP等操作系统，PHP，C#，Java等开发环境。

Oracle 10g正则表达式提高了SQL灵活性。有效的解决了数据有效性， 重复词的辨认, 无关的空白检测，或者分解多个正则组成
的字符串等问题。

Oracle 10g支持正则表达式的四个新函数分别是：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和REGEXP_REPLACE。
它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。

特殊字符：
'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 'n' 或 'r'。
'.' 匹配除换行符 n之外的任何单字符。
'?' 匹配前面的子表达式零次或一次。
'*' 匹配前面的子表达式零次或多次。
'+' 匹配前面的子表达式一次或多次。
'( )' 标记一个子表达式的开始和结束位置。
'[]' 标记一个中括号表达式。
'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。
'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。
num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。
正则表达式的一个很有用的特点是可以保存子表达式以后使用， 被称为Backreferencing. 允许复杂的替换能力
如调整一个模式到新的位置或者指示被代替的字符或者单词的位置. 被匹配的子表达式存储在临时缓冲区中，
缓冲区从左到右编号, 通过数字符号访问。 下面的例子列出了把名字 aa bb cc 变成
cc, bb, aa.
Select REGEXP_REPLACE('aa bb cc','(.*) (.*) (.*)', '3, 2, 1') FROM dual；
REGEXP_REPLACE('ELLENHILDISMIT
cc, bb, aa
'' 转义符。

字符簇：
[[:alpha:]] 任何字母。
[[:digit:]] 任何数字。
[[:alnum:]] 任何字母和数字。
[[:space:]] 任何白字符。
[[:upper:]] 任何大写字母。
[[:lower:]] 任何小写字母。
[[:punct:]] 任何标点符号。
[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。

各种操作符的运算优先级
转义符
(), (?:), (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和顺序
| “或”操作

一、REGEXP_LIKE

select * from test where regexp_like(mc,'^a{1,3}');
select * from test where regexp_like(mc,'a{1,3}');
select * from test where regexp_like(mc,'^a.*e$');
select * from test where regexp_like(mc,'^[[:lower:]]|[[:digit:]]');
select * from test where regexp_like(mc,'^[[:lower:]]');
Select mc FROM test Where REGEXP_LIKE(mc,'[^[:digit:]]');
Select mc FROM test Where REGEXP_LIKE(mc,'^[^[:digit:]]');

二、REGEXP_INSTR

Select REGEXP_INSTR(mc,'[[:digit:]]$') from test;
Select REGEXP_INSTR(mc,'[[:digit:]]+$') from test;
Select REGEXP_INSTR('The price is $400.','$[[:digit:]]+') FROM DUAL;
Select REGEXP_INSTR('onetwothree','[^[[:lower:]]]') FROM DUAL;
Select REGEXP_INSTR(',,,,,','[^,]*') FROM DUAL;
Select REGEXP_INSTR(',,,,,','[^,]') FROM DUAL;

三、REGEXP_SUBSTR

SELECT REGEXP_SUBSTR(mc,'[a-z]+') FROM test;
SELECT REGEXP_SUBSTR(mc,'[0-9]+') FROM test;
SELECT REGEXP_SUBSTR('aababcde','^a.*b') FROM DUAL;

四、REGEXP_REPLACE

Select REGEXP_REPLACE('Joe Smith','( ){2,}', ',') AS RX_REPLACE FROM dual;
Select REGEXP_REPLACE('aa bb cc','(.*) (.*) (.*)', '3, 2, 1') FROM dual；

 

匹配字符：

 

 

字符

 

功能

 

^

 

标记行的开始

 

$

标记行的结束

 

= =

匹配字符族，如：[=a=] 可匹配字符a、â、ã、ä、å等

 

.

匹配任何字符(除了NULL)

 

[:alnum:]

匹配任何字母和数字

 

[:alpha:]

匹配任何字母

 

[:blank:]

匹配空格

 

[:cntrl:]

匹配任何控制字符(在ASCII码表中不可打印的字符，如ESC键)

 

[:digit:]

任何数字

 

[:graph:]

任何 标点符号、大小写字母以及数字

 

[:lower:]

任何小写字母

 

[:print:]

任何可打印的字符

 

[:punct:]

任何标点符号，如,.+-=等

 

[:space:]

任何空白字符，如回车、换行、制表等

 

[:upper:]

任何大写字母

 

[:xdigit:]

任何十六进制数字，即0-9A-F

 

|

分隔符

 

()

子表达式，可作为量词或向后引用的前段

 

[char]

匹配括号中的任何字符，[^char]表示除开括号中字符后的任何字符

 

 

量词字符：

 

 

字符

 

功能

 

*

匹配0次或多次

 

?

匹配0次或1次

 

+

匹配1次或多次

 

{m}

匹配m次

 

{m,}

匹配至少m次

 

{m,n}

匹配至少m次，最多n次

 

/n

引用第n个匹配

 

匹配选项： 

 

 

字符

 

功能

 

c

匹配时区分大小写

 

i

匹配时不区分大小写

 

m

源字符串为多行匹配

 

n

.可匹配任何换行符

 

 

函数：

1、REGEXP_LIKE

    是LIKE语句的正则表达式版本

    语法：REGEXP_LIKE(源字符串, 匹配表达式[,匹配选项])

    例子：

    SELECT product_name FROM oe.product_information WHERE regexp_like (product_name, 'SS[PS]/[VS]');

    SS[PS]/[VS]匹配：SSP / V 、SSP / S 、 SSS / V 、 SSS / S 

2、REGEXP_INSTR

    返回源字符串中首次匹配正则表达式的起始位置

    语法：REGEXP_INSTR(srcstr, pattern [, position [, occurrence

               [, return_option [, match_option]]]])

            srcstr：源字符串

            pattern：正则表达式

            position：搜索开始位置

            occurrence：返回第几个匹配项

            return_option：返回选项，0表示开始位置，1表示返回匹配的结束位置

            match_option：匹配选项

SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT FROM dual; --返回5

 

SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 1, 1, 'i') RESULT FROM dual; --返回9

 

SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 2, 0, 'i') RESULT FROM dual; --返回28

 

SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 2, 1, 'i') RESULT FROM dual; --返回32

    

    --返回try或trying或tried或tries的位置

    SELECT regexp_instr('We are trying to make the subject easier.',

                      'tr(y(ing)?|(ied)|(ies))') resultnum

    FROM dual;

 

3、REGEXP_SUBSTR

    返回源串中匹配正则表达式的子字符串

    语法：SUBSTR(srcstr, pattern [, position 

                [, occurrence [, match_option]]]) 

            srcstr：源字符串

            pattern：正则表达式

            position：搜索的开始位置

            occurrence：返回第几个匹配的字符串

            match_option：匹配选项

   

4、REGEXP_REPLACE

    用执行字符串替换源文本中与正则表达式匹配的字符串

    语法：REGEXP_REPLACE(srcstr, pattern [,replacestr [, position

                 [, occurrence [, match_option]]]])

            srcstr：源字符串

            pattern：正则表达式

            replacestr：新的字符串

            position：搜索起始位置

            occurrence：第几个匹配项

            match_option：匹配选项

    例子：

    --返回George Mc Govern

    SELECT regexp_replace('George McGovern', '([[:lower:]])([[:upper:]])', '/1 /2') city

 

 

 

Oracle 10g提供了四个regexp function: REGEXP_LIKE , REGEXP_REPLACE , REGEXP_INSTR , REGEXP_SUBSTR 。

　　Sql代码

　　REGEXP_LIKE：比较一个字符串是否与正则表达式匹配

　　(srcstr, pattern [, match_option])

　　REGEXP_INSTR：在字符串中查找正则表达式，并且返回匹配的位置

　　(srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]])

　　REGEXP_SUBSTR：返回与正则表达式匹配的子字符串

　　(srcstr, pattern [, position [, occurrence [, match_option]]])

　　REGEXP_REPLACE：搜索并且替换匹配的正则表达式

　　(srcstr, pattern [, replacestr [, position [, occurrence [, match_option]]]])  其中各参数的含义为:

　　Sql代码

　　srcstr:        被查找的字符数据。

　　pattern:       正则表达式。

　　occurrence:    出现的次数。默认为1。

　　position:      开始位置

　　return_option: 默认值为0，返回该模式的起始位置；值为1则返回符合匹配条件的下一个字符的起始位置。

replacestr:    用来替换匹配模式的字符串。

　　match_option:  匹配方式选项。缺省为c。

　　c：case sensitive

　　I：case insensitive

　　n：(.)匹配任何字符（包括newline)

　　m：字符串存在换行的时候被作为多行处理

　　下面通过一些具体的例子来说明如何使用这四个函数。首先创建一个测试数据表，

　　Sql代码

　　SQL> create table person (

　　2  first_name varchar(20),

　　3  last_name varchar(20),

　　4  email varchar(100),

　　5  zip varchar(6));

　　Table created.

　　SQL> insert into person values ('Steven', 'Chen', '[e-mail]', '123456');

　　1 row created.

　　SQL> insert into person values ('James', 'Li', '[e-mail]' || chr(10) || '[e-mail]', '1b3d5f');

　　1 row created.

　　SQL> commit;

　　Commit complete.

　　SQL> select * from person;

　　FIRST_NAME LAST_NAME  EMAIL                ZIP

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

　　Steven     Chen       [e-mail]        123456

　　James      Li         [e-mail]      1b3d5f

　　[e-mail]

　　1。REGEXP_LIKE

　　Sql代码

　　SQL> select zip as invalid_zip from person where regexp_like(zip, '[^[:digit:]]');

　　INVALID_ZIP

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

　　1b3d5f

　　SQL> select first_name from person where regexp_like(first_name, '^S.*n$');

　　FIRST_NAME

　　----------

　　Steven

　　SQL> select first_name from person where regexp_like(first_name, '^s.*n$');

　　no rows selected

　　SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'c');

　　no rows selected

　　SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'i');

　　FIRST_NAME

　　----------

　　Steven

　　SQL> select email from person where regexp_like(email, '^james.*com$');

　　no rows selected

　　SQL> select email from person where regexp_like(email, '^james.*com$', 'n');

　　EMAIL

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

　　[e-mail]

　　[e-mail]

　　SQL> select email from person where regexp_like(email, '^li.*com$');

　　no rows selected

　　SQL> select email from person where regexp_like(email, '^li.*com$', 'm');

　　EMAIL

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

　　[e-mail]

　　[e-mail]

　　2。REGEXP_INSTR

　　Sql代码

　　查找zip中第一个非数字字符的位置

　　SQL> select regexp_instr(zip, '[^[:digit:]]') as position from person;

　　POSITION

　　----------

　　0

　　2

　从第三个字符开始，查找zip中第二个非数字字符的位置

　　SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2) as position from person;

　　POSITION

　　----------

　　0

　　6

　　从第三个字符开始，查找zip中第二个非数字字符的下一个字符位置

　　SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2, 1) as position from person;

　　POSITION

　　----------

　　0

　　7

　　3。REGEXP_SUBSTR

　　Sql代码

　　SQL> select regexp_substr(zip, '[^[:digit:]]') as zip from person;

　　ZIP

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

　　b

　　SQL> select regexp_substr(zip, '[^[:digit:]]', 3, 2) as zip from person;

　　ZIP

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

　　f

　　4。REGEXP_REPLACE

　　Sql代码

　　把zip中所有非数字字符替换为0

　　SQL> update person set zip=regexp_replace(zip, '[^[:digit:]]', '0')

　　2  where regexp_like(zip, '[^[:digit:]]');

　　1 row updated.

　　SQL> select zip from person;

　　ZIP

　　------

　　123456

　　103050

　　后向引用（backreference）：

　　后向引用是 一个很有用的特性。它能够把子表达式的匹配部分保存在临时缓冲区中，供以后重用 。缓冲区从左至右进行编号，并利用 /digit 符号进行访问。子表达式用一组圆括号来显示。利用后向引用可以实现较复杂的替换功能。

　　Sql代码

　　SQL> select regexp_replace('Steven Chen', '(.*) (.*)', '/2, /1') as reversed_name from dual;

　　REVERSED_NAME

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

　　Chen, Steven

　　在DDL中也可以正则表达式，比如Constraint, index, view

　　Sql代码

　　SQL> alter table person add constraint constraint_zip check (regexp_like(zip, '^[[:digit:]]+$'));

　　SQL> create index person_idx on person(regexp_substr(last_name, '^[[:upper:]]'));