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‘,‘[email?protected]‘,‘123456‘);
insert into person values(‘James‘,‘Li‘,‘[email?protected]‘||chr(10)||‘[email?protected]‘,‘1b3d5f‘);
insert into person values(‘Tina‘,‘Zhang‘,‘[email?protected]‘,‘2456hd‘);
insert into person values(‘Tom‘,‘Feng‘,‘[email?protected]‘,‘a654e5‘);
insert into person values(‘Jonson‘,‘zhao‘,‘[email?protected]‘,‘edjksk‘);
insert into person values(‘Vines‘,‘Wu‘,‘[email?protected]‘,‘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,‘m‘);
select email from person where regexp_like(email,‘^li.*com$‘);
select email from person where regexp_like(email,‘^li.*com$‘,‘n‘);
/*regexp_instr()===================================================================================
regexp_instr(x,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.*,‘[^[:digit:]]‘,2,1) as position from person a ;?
--查找zip中,从第1位开始第2次匹配的最后一个字符位置+1
select a.*,1,1) as position from person a ;?
--查找zip中,从第1位开始第1次匹配的最后一个字符位置+1
select a.*,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) 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) 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}‘,0) FROM DUAL;
--返回22,从第1个字符开始,找出l开头的 后跟4个任意字母的单词 第1次匹配的最后一个字符的位置+1,这里是light中t的位置+1?
SELECT REGEXP_INSTR(‘But,1) FROM DUAL;?
/*regexp_substr()===================================================================================
regexp_substr(x,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.*,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) from dual;?
select regexp_substr(‘i love oracle very much‘,‘o.*e‘,5,1) from dual;
/*regexp_replace()===================================================================================
regexp_replace(x,replace_string[,occurrence[match_option]]]])
参数说明:
x 待匹配的字符串
pattern 正则表达式元字符构成的匹配模式
replace_string 替换字符串?
start 开始匹配位置,如果不指定默认为1
occurrence 匹配的次数,如果不指定,默认为1
match_option 意义同regexp_like的一样=================================================================================================*/--把zip中所有非数字字符替换为0update person set zip=regexp_replace(zip,‘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 awhere regexp_substr(last_name,‘^[[:upper:]]‘)=‘W‘-- 用‘XXX’代替‘light’,返回But,soft! What XXX through yonder window breaks??SELECT REGEXP_REPLACE(‘But,‘XXX‘) FROM DUAL;--用’XXX‘代替以e开始,长度为3的字符串,从第5个字符开始,匹配2次select regexp_replace(‘hello everybody,may I have your attention please?‘,‘e[[:alpha:]]{2}‘,‘XXX‘,2) from dual;--大小写敏感select regexp_replace(‘hello Everybody,1) from dual;--忽略大小写select regexp_replace(‘hello Everybody,‘i‘) from dual;