oracle正则表达式的使用
来源：互联网 发布：mac系统磁盘空间不足 编辑：程序博客网 时间：2024/04/30 21:26
原文链接：http://blog.sina.com.cn/s/blog_69e7b8d701012tuj.html
在oracle数据库当中有字符处理的函数，比如substr()、replace()和instr()，当然也有做字符匹配的操作符like，不过这些函数和操作符在处理一些复杂的字符串时，会显得有些力不从心。从oracle 10g开始，Oracle内建了符合IEEE POSIX (Portable Operating System for Unix)标准的正则表达式。当然oracle也支持perl的正则表达式规则。熟练使用正则表达式，可以写出简洁，强大的SQL语句。实际上，它们类似于已有的操作符，但现在增加了强大的模式匹配功能。被搜索的数据可以是简单的字符串或是存储在数据库字符列中的大量文本。正规表达式让您能够以一种您以前从未想过的方式来搜索、替换和验证数据，并提供高度的灵活性。同时，正则表达式对oracle的约束也给与了加强。
正规表达式
正规表达式由一个或多个字符型文字或元字符组成。在最简单的格式下，正规表达式仅由字符文字组成，如正规表达式 cat。它被读作字母 c，接着是字母 a 和 t，这种模式匹配 cat、location和 catalog 之类的字符串。元字符提供算法来确定 Oracle 如何处理组成一个正规表达式的字符。当您了解了各种元字符的含义时，您将体会到正规表达式用于查找和替换特定的文本数据是非常强大的。如果我们简单理解的话，正则表达式就是一种字符串的组成和表示方法。
在使用这个新功能之前，您需要了解一些元字符的含义。句号 (.) 匹配一个正规表达式中的任意字符（除了换行符）。例如，正规表达式 a.b 匹配的字符串中首先包含字母 a，接着是其它任意单个字符（除了换行符），再接着是字母 b。字符串 axb、xaybx 和 abba 都与之匹配，因为在字符串中隐藏了这种模式。如果您想要精确地匹配以 a 开头和以 b 结尾的一条三个字母的字符串，则您必须对正规表达式进行定位。脱字符号 (^) 元字符指示一行的开始，而美元符号 ($) 指示一行的结尾。因此， 正规表达式 ^a.b$ 匹配字符串 aab、abb 或 axb。将这种方式与 LIKE 操作符提供的类似的模式匹配 a_b 相比较，其中 "_" 是单字符通配符。
下表是正则表达式部分元字符的解释（符合POSIX标准）：
^
使表达式定位至一行的开头
$
使表达式定位至一行的末尾
*
匹配 0 次或更多次
?
匹配 0 次或 1 次
+
匹配 1 次或更多次
{ｍ}
正好匹配 m 次
{ｍ，}
至少匹配 m 次
{ｍ，ｎ}
至少匹配 m 次但不超过 n 次
[:alpha:]
字母字符
[:lower:]
小写字母字符
[:upper:]
大写字母字符
[:digit:]
数字
[:alnum:]
字母数字字符
[:space:]
空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符
[:punct:]
标点字符
（...）
将子表达式分组为一个替换单元、量词单元或后向引用单元
[...]
匹配列表中的字符
[^...]
匹配不在列表中的字符
Oracle提供了四个支持正则表达式的函数:
REGEXP_LIKE , REGEXP_REPLACE , REGEXP_INSTR , REGEXP_SUBSTR 。
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]]]])
其中各参数的含义为:
srcstr:
pattern:
occurrence:
position:
return_option: 默认值为0，返回该模式的起始位置；值为1则返回符合匹配条件的下一个字符的起始位置。
replacestr:
match_option:
下面通过一些具体的例子来说明如何使用这四个函数。在测试当中，你就会逐步的体会到这些正则表达式的优势
首先创建测试表TEST，并加载测试数据：
scott@DB01> create table test (c1 int , testcol varchar2(100));
Table created.
scott@DB01> insert into test values(100,'10d6h2');
1 row created.
scott@DB01> insert into test values(110,'100025');
1 row created.
scott@DB01> insert into test values(120,'gift');
1 row created.
scott@DB01> insert into test values(130,'01083697902');
1 row created.
scott@DB01> insert into test values(140,'010-400-7591');
1 row created.
scott@DB01> insert into test values(150,'ab c de');
1 row created.
scott@DB01> insert into test values(160,'abcde');
1 row created.
scott@DB01> insert into test values(170,'tianjie@oracle.com'|| chr(10) ||'liuy@sina.com.cn');
1 row created.
scott@DB01> insert into test values(180,'Steven');
1 row created.
scott@DB01> insert into test values(190,'bac');
1 row created.
scott@DB01> insert into test values(200,'Stephen');
1 row created.
scott@DB01> commit;
Commit complete.
scott@DB01> col testcol for a60
scott@DB01> select * from test;
------------------------------------------------------------
11 rows selected.
一、REGEXP_LIKE测试
1.找到表中testcol列只包含数字的记录
scott@DB01> select * from test where regexp_like(testcol,'^[0-9]+$');
---------- ------------------------------------------------------------
2.找到表中testcol列只包含6个数字的记录
scott@DB01> select * from test where regexp_like(testcol,'^[0-9]{6}$');
---------- ------------------------------------------------------------
3.当然上面2的语句也可以使用下面的写法
scott@DB01> select * from test where regexp_like(testcol,'^\d{6}$');
---------- ------------------------------------------------------------
4.
scott@DB01> select * from test where regexp_like(testcol,'^Ste(v|ph)en$');
---------- ------------------------------------------------------------
5.上面的语句默认是区分大小写的，如果要不区分大小写的话，可以考虑使用'i'
scott@DB01> select * from test where regexp_like(testcol,'^ste(v|ph)en$');
no rows selected
scott@DB01> select * from test where regexp_like(testcol,'^ste(v|ph)en$','i');
---------- ------------------------------------------------------------
6.找到表中testcol列只包含字母字符的记录
scott@DB01> select * from test where regexp_like(testcol,'^[[:alpha:]]+$');
---------- ------------------------------------------------------------
7.如果想要找到表test中的testcol列以liu开头，中间字符任意，组后以cn结束，直接写语句是不行的，需要用到'm',视记录为多行
scott@DB01> select * from test where regexp_like(testcol,'^liu.*cn$');
no rows selected
scott@DB01> select * from test where regexp_like(testcol,'^liu.*cn$','m');
---------- ------------------------------------------------------------
1.返回表中testcol列第一个非数字字符出现的位置
scott@DB01> col testcol for a30
scott@DB01> set linesize 140
scott@DB01> select c1,testcol,regexp_instr(testcol,'\D') from test;
---------- ------------------------------ --------------------------