Oracle 支持正则表达式的函数

内容提要
oracle 10g 增加的正则表达式函数有以下四种：
regexp_like() --返回满足条件的字段
regexp_instr() --返回满足条件的字符或字符串的位置
regexp_replace() --返回替换后的字符串
regexp_substr() --返回满足条件的字符或字符串
这四个函数的功能分别对应传统sql的 like操作符 和 instr 、replace 、substr函数，
在一般的、不怎么复杂的模式匹配中，使用传统的sql函数就能满足，当需要实现的操作
比较复杂时，使用正则表达式函数，就可以写出简洁、强大的sql语句。
匹配过程中可能会涉及到的元字符（Meta Character）对应的sql代码：
^ 使表达式定位至一行的开头
$ 使表达式定位至一行的末尾
* 匹配0次或更多次
? 匹配0次或1次
+ 匹配1次或更多次
{m} 正好匹配m次
{m,} 至少匹配m次
{m,n} 至少匹配m次但不超过n次
[:alpha:] 字母字符，匹配字符A-Z、a-z
[:lower:] 小写字母字符,匹配字符a-z
[:upper:] 大写字母字符,匹配字符A-Z
[:digit:] 数字,匹配数字0-9
[:alphanum:] 字母数字字符,匹配字符A-Z、a-z、0-9
[:space:] 空白字符（禁止打印），如回车、换行符、竖直制表符和换页符[:punct:] 标点字符
[:cntrl:] 控制字符（禁止打印）
[:print:] 可打印字符|分隔替换选项，通常与分组符()一期使用
() 将子表达式分组为一个替换单元，量词单元或后向引用单元
[char] 字符列表
. 匹配除null之外的任意单个字符
/ 要匹配的字符是一个特殊字符、常量或者后者引用
x|y 匹配 x 或 y
[abc] 匹配abc中的任何单个字符
[a-z] 匹配 a 到 z 范围内的任意单个字符


以下为测试用例：
/*创建测试表==================================================================================*/
create table person
(
first_name varchar2(20),
last_name varchar2(20),
email varchar2(100),
zip varchar(6)
);
insert into person values('Steven','Chen','steven@hp.com','123456');
insert into person values('James','Li','jamesli@sum.com'||chr(10)||'lijames@oracle.com','1b3d5f');
insert into person values('Tina','Zhang','chillaxzx@163.com','2456hd');
insert into person values('Tom','Feng','Tomfeng@126.com','a654e5');
insert into person values('Jonson','zhao','Jonson@google.com','edjksk');
insert into person values('Vines','Wu','Vines@162.com','2djks4');
commit;
select * from person;

/*regexp_like()=================================================================================
regexp_like(search_string ,pattern[,match_option]);
参数说明：
search_string是搜索值
pattern 正则表达式元字符构成的匹配模式,长度限制在512字节内
match_option是一个文本串，允许用户设置该函数的匹配行为。可以使用的选项有：
c 匹配时，大小写敏感，默认值
i 匹配时，大小写不敏感
n 允许使用原点（.）匹配任何新增字符
m 允许将源字符作为多个字符串对待
===============================================================================================*/
--zip字段以数字开始
select * from person where regexp_like(zip,'^[[:digit:]]');
--注意区别
select * from person where regexp_like(zip,'[^[:digit:]]'); --包含非数字
select * from person where regexp_like(zip,'[[:digit:]]'); --包含数字
--zip字段以数字结尾
select * from person where regexp_like(zip,'[[:digit:]]$');
--first_name字段以S开始，N结束的，不区分大小写
select * from person where regexp_like(first_name,'^s.*N$','i'); --注意这里的'i'不能是‘I’，否则报错
--email字段,注意区别
select email from person where regexp_like(email, '^james.*com$');
select email from person where regexp_like(email, '^james.*com$', 'n');
select email from person where regexp_like(email, '^james.*com$', 'm');
select email from person where regexp_like(email, '^li.*com$');
select email from person where regexp_like(email, '^li.*com$', 'm');
select email from person where regexp_like(email, '^li.*com$', 'n');

/*regexp_instr()===================================================================================
regexp_instr(x,pattern[,start[,occurrence[,return_option[,match_option]]]])
参数说明：
x 待匹配的字符串
pattern 正则表达式元字符构成的匹配模式
start 开始匹配位置，如果不指定默认为1
occurrence 匹配的次数，如果不指定，默认为1
return_option 指定返回的类型，若该参数为0，则返回值为匹配位置的第一个字符，若为非0，则返回匹配值的最后一个位置+1
match_option 意义同regexp_like的一样
=================================================================================================*/
--查找zip中第一个非数字字符的位置
select a.*, regexp_instr(zip,'[^[:digit:]]') as position from person a ;
--查找zip中，从第2位开始第2次匹配的最后一个字符位置+1
select a.*, regexp_instr(zip,'[^[:digit:]]',2,2,1) as position from person a ;
--查找zip中，从第1位开始第2次匹配的最后一个字符位置+1
select a.*, regexp_instr(zip,'[^[:digit:]]',1,2,1) as position from person a ;
--查找zip中，从第1位开始第1次匹配的最后一个字符位置+1
select a.*, regexp_instr(zip,'[^[:digit:]]',1,1,1) as position from person a ;
--返回21，从第1个字符开始，找出以o开头 以e结尾 中间跟4个任意字母的单词 第1次匹配的第1个字符位置，这里是oracle中o的位置
select regexp_instr('i love http://www.52oracle.com','o[[:alpha:]]{4}e',1,1) as r from dual
--返回24，从第1个字符开始，找出以o开头 后跟2个任意字母的单词 第2次匹配的第1个字符位置，这里是ora中o的位置
select regexp_instr('i love http://www.52oracle.com','o[[:alpha:]]{2}',1,2,1) as r from dual
--返回17，从第1个字符开始，找出l开头的 后跟4个任意字母的单词 第1次匹配的第1个字符的位置，这里是light中l的位置
SELECT REGEXP_INSTR('But, soft! What light through yonder window breaks？','l[[:alpha:]]{4}',1,1,0) FROM DUAL;
--返回22，从第1个字符开始，找出l开头的 后跟4个任意字母的单词 第1次匹配的最后一个字符的位置+1，这里是light中t的位置+1
SELECT REGEXP_INSTR('But, soft! What light through yonder window breaks？','l[[:alpha:]]{4}',1,1,1) FROM DUAL;

/*regexp_substr()===================================================================================
regexp_substr(x,pattern[,start[,occurrence[match_option]]])
参数说明：
x 待匹配的字符串
pattern 正则表达式元字符构成的匹配模式
start 开始匹配位置，如果不指定默认为1
occurrence 匹配的次数，如果不指定，默认为1
match_option 意义同regexp_like的一样
=================================================================================================*/
--查找第一个非0字符值
select a.* , regexp_substr(zip, '[^[:digit:]]') as zip from person a ;
--从第三个字符开始，查找第2个非0字符值
select a.* , regexp_substr(zip, '[^[:digit:]]', 3, 2) as zip from person a;
--查找以o开始的，后面接4个字母的字符串
select regexp_substr('I love oracle very much','o[[:alpha:]]{4}') from dual;
--查找以o开始的，后面至少接4个字母的字符串
select regexp_substr('I love oracle very much','o[[:alpha:]]{4,}') from dual;
--查找以o开始，以e结尾的，后面接1个或多个除null之外字符的字符串
select regexp_substr('i love oracle very much','o.{1}e',1,1) from dual;
select regexp_substr('i love oracle very much','o.*e',5,1) from dual;

/*regexp_replace()===================================================================================
regexp_replace(x,pattern[,replace_string[,start[,occurrence[match_option]]]])
参数说明：
x 待匹配的字符串
pattern 正则表达式元字符构成的匹配模式
replace_string 替换字符串
start 开始匹配位置，如果不指定默认为1
occurrence 匹配的次数，如果不指定，默认为1
match_option 意义同regexp_like的一样
=================================================================================================*/
--把zip中所有非数字字符替换为0
update person set zip=regexp_replace(zip, '[^[:digit:]]', '0')where regexp_like(zip, '[^[:digit:]]');
select zip from person;
--后向引用：
--后向引用是 一个很有用的特性。它能够把子表达式的匹配部分保存在临时缓冲区中，供以后重用 。缓冲区从左至右进行编号，
--并利用 \digit 符号进行访问。子表达式用一组圆括号来显示。利用后向引用可以实现较复杂的替换功能。
select regexp_replace('Steven Chen', '(.*) (.*)', '\2, \1') as reversed_name from dual;
--修改person表，增加检查zip的第一个字符是否为数字的检查索引
alter table person add constraint constraint_zip check (regexp_like(zip, '^[[:digit:]]+$'));
--根据姓的第一个大写字母创建索引
create index person_idx on person(regexp_substr(last_name, '^[[:upper:]]'));
select *
from person a
where regexp_substr(last_name, '^[[:upper:]]')='W'
-- 用‘XXX’代替‘light’ , 返回But, soft! What XXX through yonder window breaks？
SELECT REGEXP_REPLACE('But, soft! What light through yonder window breaks？','l[[:alpha:]]{4}','XXX') FROM DUAL;
--用’XXX‘代替以e开始,长度为3的字符串，从第5个字符开始，匹配2次
select regexp_replace('hello everybody,may I have your attention please?','e[[:alpha:]]{2}','XXX',5,2) from dual;
--大小写敏感
select regexp_replace('hello Everybody,may I have your attention please?','e[[:alpha:]]{2}','XXX',5,1) from dual;
--忽略大小写
select regexp_replace('hello Everybody,may I have your attention please?','e[[:alpha:]]{2}','XXX',5,2,'i') from dual;