ORACLE终于在10G中提供了对正则表达式的支持，以前那些需要通过LIKE来进行的复杂的匹配就可以通过使用正则表达式更简单的实现。
ORACLE中的支持正则表达式的函数主要有下面四个：
1，REGEXP_LIKE ：与LIKE的功能相似
2，REGEXP_INSTR ：与INSTR的功能相似
3，REGEXP_SUBSTR ：与SUBSTR的功能相似
4，REGEXP_REPLACE ：与REPLACE的功能相似
在新的函数中使用正则表达式来代替通配符‘%’和‘_’。
正则表达式由标准的元字符（metacharacters）所构成：
'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 'n' 或 'r'。
'.' 匹配除换行符 n之外的任何单字符。
'?' 匹配前面的子表达式零次或一次。
'+' 匹配前面的子表达式一次或多次。
'*' 匹配前面的子表达式零次或多次。
'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。
'( )' 标记一个子表达式的开始和结束位置。
'[]' 标记一个中括号表达式。
'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。
num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。
字符簇：
[[:alpha:]] 任何字母。
[[:digit:]] 任何数字。
[[:alnum:]] 任何字母和数字。
[[:space:]] 任何白字符。
[[:upper:]] 任何大写字母。
[[:lower:]] 任何小写字母。
[[:punct:]] 任何标点符号。
[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。
各种操作符的运算优先级
转义符
(), (?:), (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和顺序
| “或”操作
下面通过几个例子来具体说明这几个新函数的使用方法：
SQL> create table sunwg (id varchar2(100));
Table created.
SQL> insert into sunwg values ('<a href="http://sunwgneuqsoft.itpub.net/post/34741/447698">常见SQL访问索引的方式</a>');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from sunwg;
ID
----------------------------------------------------------------------------------------------------
<a href="http://sunwgneuqsoft.itpub.net/post/34741/447698">常见SQL访问索引的方式</a>
1， REGEXP_LIKE
REGEXP_LIKE与LIKE类似，用REGEXP_LIKE能实现的操作大部分都可以用LIKE实现，不过要简单方便得多。
<a>目标：查询表sunwg中是否存在类似与3XX41的记录？
LIKE：
select * from sunwg where id like '%3__41%';
REGEXP_LIKE
select * from sunwg where regexp_like(id,'3..41');
<b>目标：查询表sunwg中是否存在类似与3XX41的记录,并且XX必须是数字？
LIKE：
这个LIKE我就想出来很好的实现办法了，唯一想到就是截取出来后判断该字符串是不是纯数字的。
REGEXP_LIKE
select * from sunwg where regexp_like(id,'3[0-9]{2}41');
用REGEXP_LIKE则可以简单快捷的得到结果。其他几个函数也都有类似的情况，下面的函数就不具体比较差异了，仅仅给出常用的用法。
2， REGEXP_INSTR
<a>目标：查询表sunwg中是否存在类似与3XX41的字符串第一次出现的位置？
SQL> select regexp_instr(id,'3..41',1,1) from sunwg;
REGEXP_INSTR(ID,'3..41',1,1)
----------------------------
46
SQL> select substr(id,46,5) from sunwg;
SUBST
-----
34741
3， REGEXP_SUBSTR
<a>目标：截取出表sunwg中的URL地址？
SQL> select regexp_substr(id,'http[0-9a-zA-Z/:.]+') from sunwg;
REGEXP_SUBSTR(ID,'HTTP[0-9A-ZA-Z/:.]+')
----------------------------------------------------------------------------------------------------
http://sunwgneuqsoft.itpub.net/post/34741/447698
4， REGEXP_REPLACE
<a>目标：替换表sunwg中的URL的地址为www.163.com?
SQL> select regexp_replace(id,'http[0-9a-zA-Z/:.]+','www.163.com') from sunwg;
REGEXP_REPLACE(ID,'HTTP[0-9A-ZA-Z/:.]+','WWW.163.COM')
------------------------------------------------------------------------------------------------------------------------------------------------------
<a href="www.163.com">常见SQL访问索引的方式</a>
从上面的例子可以看得出来这几个支持正则表达式的函数是十分强大的，合理的加以使用一定会使你写出的SQL更加简单高效。
regexp_substr
regexp_substr (string, pattern, position)
regexp_substr (string, pattern, position, occurence)
regexp_substr (string, pattern, position, occurence, parameters)
parameters can be a combination of
regexp_substr (string, pattern)
i: to match case insensitively c: to match case sensitively n: to make the dot (.) match new lines as well m: to make ^ and $ match beginning and end of a line in a multiline string
regexp_substr is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
substr.
Links
See also
On splitting a string into words with regular expressions where a function uses
regexp_substr to split a string.
Then there is also
safe_to_number() where regexp_substr is used to convert strings to numbers.
regexp_instr
regexp_instr (string, pattern)
regexp_instr (string, pattern, position)
regexp_instr (string, pattern, position, occurence)
regexp_instr (string, pattern, position, occurence, return-option)
regexp_instr (string, pattern, position, occurence, return-option, parameters)
Parameters
parameters can be a combination of
i: to match case insensitively c: to match case sensitively n: to make the dot (.) match new lines as well m: to make ^ and $ match beginning and end of a line in a multiline string x: to ignore white spaces.
regexp_instr is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
instr.
regexp_like
regexp_like (string, pattern);
regexp_like (string, pattern, parameters);
parameters can be a combination of
i: to match case insensitively c: to match case sensitively n: to make the dot (.) match new lines as well m: to make ^ and $ match beginning and end of a line in a multiline string
regexp_like is an Oracle SQL function that enables
regular expressions in queries. It enhances the «traditional»
like.
regexp_like is a
pattern condition.
Demonstration
create table strings (
str varchar2(30)
);
create table patterns (
pat varchar2(50),
dsc varchar2(30)
);
insert into patterns values ('^[[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}
insert into strings values ('987-65-4321');
insert into strings values ('hello foo bar');
insert into strings values ('4987-65-4321');
insert into strings values ('hello FOO BAR');
insert into strings values ('-4.55');
insert into strings values ('987-65-43213');
insert into strings values ('4.55');
insert into strings values ('hello bar bar');
insert into strings values (' 4.55');
insert into strings values ('1234567890');
insert into strings values ('hello FOO FOO');
select
str,dsc
from
strings
cross join patterns
where
regexp_like(str, pat) ;
STR DSC
------------------------------ ------------------------------
987-65-4321 Social security number
hello bar bar Repeated words
hello FOO FOO Repeated words
hello foo bar Only lowercase words
hello bar bar Only lowercase words
1234567890 Only digits
987-65-4321 At least one digit
4987-65-4321 At least one digit
-4.55 At least one digit
987-65-43213 At least one digit
4.55 At least one digit
4.55 At least one digit
1234567890 At least one digit
-4.55 Number
4.55 Number
1234567890 Number
regexp_replace
regexp_replace (string, pattern)
regexp_replace (string, pattern, replace-string)
regexp_replace (string, pattern, replace-string, position)
regexp_replace (string, pattern, replace-string, position, occurence)
regexp_replace (string, pattern, replace-string, position, occurence, parameters)
parameters can be a combination of
i: to match case insensitively c: to match case sensitively n: to make the dot (.) match new lines as well m: to make ^ and $ match beginning and end of a line in a multiline string
regexp_substr is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
substr.
regexp_replace is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
replace.
Demonstration
create table strings (
str varchar2(30)
);
create table patterns (
pat varchar2(60),
repl varchar2(30),
dsc varchar2(30)
);
insert into patterns values ('^[[:space:]]*[^[:space:]]+[[:space:]]+([^[:space:]]+).*', '/1', 'The 2nd word');
insert into patterns values ('^[^[:digit:]]*([[:digit:]]*/.?[[:digit:]]+).*' , '/1', 'The 1st number');
insert into patterns values ('^[^[:upper:]]*([[:upper:]]+).*' , '/1', 'Uppercase word');
insert into strings values ('foo bar baz');
insert into strings values ('bla MOO 82.22 7.34 bla');
insert into strings values (' one two 3 four ');
column found format a20
select
str,
regexp_replace(str, pat, repl) found,
dsc
from
strings
cross join patterns
where
regexp_instr(str,pat) > 0;
STR FOUND DSC
------------------------------ -------------------- --------------------
foo bar baz bar The 2nd word
bla MOO 82.22 7.34 bla MOO The 2nd word
one two 3 four two The 2nd word
bla MOO 82.22 7.34 bla 82.22 The 1st number
one two 3 four 3 The 1st number
bla MOO 82.22 7.34 bla MOO Uppercase word
Links
See also
On using regexp_replace to format data.
, 'Social security number'); insert into patterns values ('[^[:alpha:]]([[:alpha:]]+)[^[:alpha:]] */1' , 'Repeated words'); insert into patterns values ('^([[:lower:]]| )*
___FCKpd___5
___FCKpd___6
___FCKpd___7
___FCKpd___8
parameters can be a combination of
i: to match case insensitively c: to match case sensitively n: to make the dot (.) match new lines as well m: to make ^ and $ match beginning and end of a line in a multiline string
regexp_substr is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
substr.
regexp_replace is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
replace.
Demonstration
___FCKpd___9
___FCKpd___10
___FCKpd___11
___FCKpd___12
___FCKpd___13
Links
See also
On using regexp_replace to format data.
, 'Only lowercase words'); insert into patterns values ('^[[:digit:]]+
___FCKpd___5
___FCKpd___6
___FCKpd___7
___FCKpd___8
parameters can be a combination of
i: to match case insensitively c: to match case sensitively n: to make the dot (.) match new lines as well m: to make ^ and $ match beginning and end of a line in a multiline string
regexp_substr is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
substr.
regexp_replace is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
replace.
Demonstration
___FCKpd___9
___FCKpd___10
___FCKpd___11
___FCKpd___12
___FCKpd___13
Links
See also
On using regexp_replace to format data.
, 'Only digits'); insert into patterns values ('[[:digit:]]' , 'At least one digit'); insert into patterns values ('^-?[[:digit:]]*/.?[[:digit:]]+
___FCKpd___5
___FCKpd___6
___FCKpd___7
___FCKpd___8
parameters can be a combination of
i: to match case insensitively c: to match case sensitively n: to make the dot (.) match new lines as well m: to make ^ and $ match beginning and end of a line in a multiline string
regexp_substr is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
substr.
regexp_replace is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
replace.
Demonstration
___FCKpd___9
___FCKpd___10
___FCKpd___11
___FCKpd___12
___FCKpd___13
Links
See also
On using regexp_replace to format data.
, 'Number');
___FCKpd___5
___FCKpd___6
___FCKpd___7
___FCKpd___8
parameters can be a combination of
i: to match case insensitively c: to match case sensitively n: to make the dot (.) match new lines as well m: to make ^ and $ match beginning and end of a line in a multiline string
regexp_substr is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
substr.
regexp_replace is an Oracle SQL function that enables
regular expressions in queries. It enhances the 'traditional'
replace.
Demonstration
___FCKpd___9
___FCKpd___10
___FCKpd___11
___FCKpd___12
___FCKpd___13
Links
See also
On using regexp_replace to format data.
