oracle 正则表达式

元字符（Meta Character）：

Sql代码

1. ^ 使表达式定位至一行的开头
2. $ 使表达式定位至一行的末尾
3. * 匹配 0 次或更多次
4. ? 匹配 0 次或 1 次
5. + 匹配 1 次或更多次
6. {m} 正好匹配 m 次
7. {m,} 至少匹配 m 次
8. {m, n} 至少匹配 m 次但不超过 n 次
9. [:alpha:] 字母字符
10. [:lower:] 小写字母字符
11. [:upper:] 大写字母字符
12. [:digit:] 数字
13. [:alnum:] 字母数字字符
14. [:space:] 空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符[:punct:] 标点字符
15. [:cntrl:] 控制字符（禁止打印）
16. [:print:] 可打印字符 | 分隔替换选项，通常与分组操作符 () 一起使用
17. ( ) 将子表达式分组为一个替换单元、量词单元或后向引用单元
18. [char] 字符列表

^ 使表达式定位至一行的开头
$ 使表达式定位至一行的末尾
* 匹配 0 次或更多次
? 匹配 0 次或 1 次
+ 匹配 1 次或更多次
{m} 正好匹配 m 次
{m,} 至少匹配 m 次
{m, n} 至少匹配 m 次但不超过 n 次
[:alpha:] 字母字符
[:lower:] 小写字母字符
[:upper:] 大写字母字符
[:digit:] 数字
[:alnum:] 字母数字字符
[:space:] 空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符[:punct:] 标点字符
[:cntrl:] 控制字符（禁止打印）
[:print:] 可打印字符 | 分隔替换选项，通常与分组操作符 () 一起使用
( ) 将子表达式分组为一个替换单元、量词单元或后向引用单元
[char] 字符列表



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

1. REGEXP_LIKE：比较一个字符串是否与正则表达式匹配
2. (srcstr, pattern [, match_option])
3.
4. REGEXP_INSTR：在字符串中查找正则表达式，并且返回匹配的位置
5. (srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]])
6.
7. REGEXP_SUBSTR：返回与正则表达式匹配的子字符串
8. (srcstr, pattern [, position [, occurrence [, match_option]]])
9.
10. REGEXP_REPLACE：搜索并且替换匹配的正则表达式
11. (srcstr, pattern [, replacestr [, position [, occurrence [, match_option]]]])

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代码

1. srcstr: 被查找的字符数据。
2. pattern: 正则表达式。
3. occurrence: 出现的次数。默认为1。
4. position: 开始位置
5. return_option: 默认值为0，返回该模式的起始位置；值为1则返回符合匹配条件的下一个字符的起始位置。
6. replacestr: 用来替换匹配模式的字符串。
7. match_option: 匹配方式选项。缺省为c。
8. c：case sensitive
9. I：case insensitive
10. n：(.)匹配任何字符（包括newline)
11. m：字符串存在换行的时候被作为多行处理

srcstr: 被查找的字符数据。
pattern: 正则表达式。
occurrence: 出现的次数。默认为1。
position: 开始位置
return_option: 默认值为0，返回该模式的起始位置；值为1则返回符合匹配条件的下一个字符的起始位置。
replacestr: 用来替换匹配模式的字符串。
match_option: 匹配方式选项。缺省为c。
c：case sensitive
I：case insensitive
n：(.)匹配任何字符（包括newline)
m：字符串存在换行的时候被作为多行处理



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

1. SQL> create table person (
2. 2 first_name varchar(20),
3. 3 last_name varchar(20),
4. 4 email varchar(100),
5. 5 zip varchar(6));
6.
7. Table created.
8.
9. SQL> insert into person values ('Steven', 'Chen', 'steven@hp.com', '123456');
10. 1 row created.
11.
12. SQL> insert into person values ('James', 'Li', 'jamesli@sun.com' || chr(10) || 'lijames@oracle.com', '1b3d5f');
13. 1 row created.
14.
15. SQL> commit;
16. Commit complete.
17.
18. SQL> select * from person;
19.
20. FIRST_NAME LAST_NAME EMAIL ZIP
21. ---------- ---------- -------------------- ------
22. Steven Chen steven@hp.com 123456
23. James Li jamesli@sun.com 1b3d5f
24. lijames@oracle.com

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', 'steven@hp.com', '123456');
1 row created.

SQL> insert into person values ('James', 'Li', 'jamesli@sun.com' || chr(10) || 'lijames@oracle.com', '1b3d5f');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from person;

FIRST_NAME LAST_NAME EMAIL ZIP
---------- ---------- -------------------- ------
Steven Chen steven@hp.com 123456
James Li jamesli@sun.com 1b3d5f
lijames@oracle.com



1。REGEXP_LIKE
Sql代码

1. SQL> select zip as invalid_zip from person where regexp_like(zip, '[^[:digit:]]');
2. INVALID_ZIP
3. --------------------
4. 1b3d5f
5.
6. SQL> select first_name from person where regexp_like(first_name, '^S.*n$');
7. FIRST_NAME
8. ----------
9. Steven
10.
11. SQL> select first_name from person where regexp_like(first_name, '^s.*n$');
12. no rows selected
13.
14. SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'c');
15. no rows selected
16.
17. SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'i');
18. FIRST_NAME
19. ----------
20. Steven
21.
22. SQL> select email from person where regexp_like(email, '^james.*com$');
23. no rows selected
24.
25. SQL> select email from person where regexp_like(email, '^james.*com$', 'n');
26. EMAIL
27. --------------------
28. jamesli@sun.com
29. lijames@oracle.com
30.
31. SQL> select email from person where regexp_like(email, '^li.*com$');
32. no rows selected
33.
34. SQL> select email from person where regexp_like(email, '^li.*com$', 'm');
35. EMAIL
36. --------------------
37. jamesli@sun.com
38. lijames@oracle.com

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
--------------------
jamesli@sun.com
lijames@oracle.com

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
--------------------
jamesli@sun.com
lijames@oracle.com



注意上面分别测试了不同的match_option对结果的影响。



2。REGEXP_INSTR
Sql代码

1. 查找zip中第一个非数字字符的位置
2. SQL> select regexp_instr(zip, '[^[:digit:]]') as position from person;
3. POSITION
4. ----------
5. 0
6. 2
7.
8. 从第三个字符开始，查找zip中第二个非数字字符的位置
9. SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2) as position from person;
10. POSITION
11. ----------
12. 0
13. 6
14.
15. 从第三个字符开始，查找zip中第二个非数字字符的下一个字符位置
16. SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2, 1) as position from person;
17. POSITION
18. ----------
19. 0
20. 7

查找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代码

1. SQL> select regexp_substr(zip, '[^[:digit:]]') as zip from person;
2. ZIP
3. ------------------
4. b
5.
6. SQL> select regexp_substr(zip, '[^[:digit:]]', 3, 2) as zip from person;
7. ZIP
8. ------------
9. f

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代码

1. 把zip中所有非数字字符替换为0
2. SQL> update person set zip=regexp_replace(zip, '[^[:digit:]]', '0')
3. 2 where regexp_like(zip, '[^[:digit:]]');
4. 1 row updated.
5.
6. SQL> select zip from person;
7. ZIP
8. ------
9. 123456
10. 103050

把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代码

1. SQL> select regexp_replace('Steven Chen', '(.*) (.*)', '\2, \1') as reversed_name from dual;
2.
3. REVERSED_NAME
4. --------------------
5. Chen, Steven

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

REVERSED_NAME
--------------------
Chen, Steven



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

1. SQL> alter table person add constraint constraint_zip check (regexp_like(zip, '^[[:digit:]]+$'));
2.
3. SQL> create index person_idx on person(regexp_substr(last_name, '^[[:upper:]]'));