SQL正则表达式函数与元字符总结_正则表达式

一、元字符

当我们要进行一些简单的糊涂查询时用百分号(%),通配符(_)就可以了.其中%表达任意长度的字符串,_表示任意的某一个字符。但如果在一些复杂的查询中关用这两个符号sql语句就会非常复杂,而且也不一定能实现.从oracle 10g开始引入了在其他程序语言中普通使用的正则表达式。主要有regexp_like（匹配）,regexp_replace（替换）,regexp_substr（提取）,regexp_instr（包含）四个正则表达式函数。

二、正则表达函数

1、regexp_like(x,pattern[,match_option])

含义：查看x是否与pattern相匹配，该函数还可以提供一个可选的参数match_option字符串说明默认的匹配选项。match_option的取值如下：

‘c’ 说明在进行匹配时区分大小写（缺省值）；
‘i’ 说明在进行匹配时不区分大小写；
‘n’ (.)点号能表示所有单个字符；
‘m’ 字符串存在换行的时候当作多行处理.这样就可匹配每行的结尾 . 不然的话就可匹配每行的结尾.不然的话就可匹配每行的结尾.不然的话只匹配字符串最后的位置。

select * from emp where regexp_like(ename,'^a[a-z]*n$')

解析：
从emp表中匹配ename字段，要求以a开头，a-z任意多个字符（包括0个），n结尾。
例如ename为arwen或arwin或anden.但arwen不能被匹配.因为默认是区分大小写。

select * from emp where regexp_like(ename,'^a[a-z]*n$','i')

可以查找ename为arwen的行记录。

2、regexp_instr(x,pattern[,start[,occurrence[,return_option[, match_option]]]])

含义：用于在x中查找pattern。返回pattern在x中出现的位置。匹配位置从1开始。可以参考字符串函数 instr()，参数相关：

‘start’ 开始查找的位置；
‘occurrence’ 说明应该返回第几次出现pattern的位置；
‘return_option’ 说明应该返回什么整数。若该参数为0，则说明要返回的整数是x中的一个字符的位置；若该参数为非0的整数，则说明要返回的整数为x中出现在pattern之后的字符的位置；
‘match_option’ 修改默认的匹配设置。与regexp_like里面的相同。

select regexp_instr('hello world','o',1,1,0);   
==》5 

解析：
查找o在hello word中第一次出现的位置。
start=1，occurrence=1，return_option=0

select regexp_instr('hello world','o',1,1,1);
==》6

查找o在hello word中第一次出现后的字符的位置，这里return_option=1也可以等于其他非0整数。

select regexp_instr('hello world','o',1,2,0);
==》8

查找o在hello word中第二次出现的位置（空格也属于一个字符位置）。

3、regexp_substr(x,pattern[,start[,occurrence[, match_option]]])

**含义：**用于在x中查找pattern并返回。可以参考字符串函数 substr()，参数同regexp_instr函数。

select regexp_substr('hello world','l{2}');            
==》ll

解析：
查询到匹配的字符串才返回匹配的字符.没查到就返回空。

4、regexp_replace(x,pattern[,replace_string[,start[,occurrence[, match_option]]]])

含义：用于在x中查找pattern，并将其替换为replae_string。可以参考字符串函数 replace()，参数同regexp_instr函数。

replace(string,from_str,to_str) 即：将string中所有出现的from_str替换为to_str。注意：from_str不能是空字符串 ('')。

select regexp_replace('hello world','o','x',1,1);            
==》hellx world

解析：
start=1，occurrence=1。

select regexp_replace('hello world','o','x');            
==》hellx wxrld

select regexp_replace('hello world','o','x',1,2);            
==》hello wxrld