Oracle 10g正则表达式的使用总结
Oracle 10g数据库内建了符合IEEE POSIX (Portable Operating System for Unix)标准的正则表达式。熟练使用正则表达式，可以写出简洁，强大的SQL语句。
ORACLE中的支持正则表达式的函数主要有下面四个：
1，REGEXP_LIKE ：与LIKE的功能相似
2，REGEXP_INSTR ：与INSTR的功能相似
3，REGEXP_SUBSTR ：与SUBSTR的功能相似
4，REGEXP_REPLACE ：与REPLACE的功能相似
这四个函数在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，但是它们使用POSIX 正则表达式代替了老的百分号(%)和通配符(_)字符。
POSIX 正则表达式由标准的元字符(metacharacters)所构成：
'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 '\n' 或 '\r'. '.' 匹配除换行符之外的任何单字符。
'？' 匹配前面的子表达式零次或一次。
'+' 匹配前面的子表达式一次或多次。
'*' 匹配前面的子表达式零次或多次。
'|' 指明两项之间的一个选择。例子'^([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 位置和顺序。
下面收集了几个大家在Oracle SQL中常用到的正则表达式以及它们的用法，欢迎大家补充。
1. 判断字符串是否是数字
正则表达式：^(\-)?\d*(\.\d+)?$
用法：
select case when REGEXP_LIKE (cx.COMP_DATA_VALUE ,'^(\-)?\d*(\.\d+)?$')
then to_number(cx.COMP_DATA_VALUE)
else null
end
from change_on_setup.COMP_EXT cx
where cx.COMP_PLAN_ID = cp.COMP_PLAN_ID
and cx.COMP_ENTRY_ID = ce.COMP_ENTRY_ID
and cx.COMP_DATA_KEY = 'BUDGETSAL_A_D'
2. 判断字符串是否是日期
判断格式为mm/dd/yyyy的日期
正则表达式：^(((0?[1-9]|1[012])/(0?[1-9]|1\d|2[0-8])|(0?[13456789]|1[012])
/(29|30)|(0?[13578]|1[02])/31)/(19|[2-9]\d)\d{2}|0?2/29/((19|[2-9]\d)(0[48]|[2468][048]|[13579][26])|(([2468][048]|[3579][26])00)))$
判断格式为dd/mm/yyyy的日期
正则表达式：^(((0?[1-9]|[012]\d|3[01])\/(0?[13578]|1[02])\/((19|[2-9]\d)
\d{2}))|((0?[1-9]|[12]\d|30)\/(0?[13456789]|1[012])\/((19|[2-9]\d)\d{2}))|((0?[1-9]|1\d|2[0-8])\/0?2\/((19|[2-9]\d)\d{2}))|(29\/0?2\/((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))$
用法：
select case when REGEXP_LIKE(ui.USERS_VALUE,
'^(((0?[1-9]|[012]\d|3[01])\/(0?[13578]|1[02])\/((19|[2-9]\d)\d{2}))|((0?[1-9]|[12]\d|30)\/(0?[13456789]|1[012])\/((19|[2-9]\d)\d{2}))|((0?[1-9]|1\d|2[0-8])\/0?2\/((19|[2-9]\d)\d{2}))|(29\/0?2\/((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))$')
then to_char(to_date(ui.USERS_VALUE, 'dd/mm/yyyy'), 'dd/mm/yyyy')
when REGEXP_LIKE(ui.USERS_VALUE,
'^(((0?[1-9]|1[012])/(0?[1-9]|1\d|2[0-8])|(0?[13456789]|1[012])/(29|30)|(0?[13578]|1[02])/31)/(19|[2-9]\d)\d{2}|0?2/29/((19|[2-9]\d)(0[48]|[2468][048]|[13579][26])|(([2468][048]|[3579][26])00)))$')
then to_char(to_date(ui.USERS_VALUE, 'mm/dd/yyyy'), 'dd/mm/yyyy')
else null
end
from change_on_setup.USERS_INFO ui
where ui.USERS_SYS_ID = u.USERS_SYS_ID
and upper(ui.USERS_KEY) = 'REVIEW_FREQ'
3.去掉字符串中的除字母数字以外的任何字符
正则表达式：[^[:alnum:]]
用法：
下面的query将非英文字符的人名(比如Şağanaç, Çiçek)转换成英文人名（Saganac, Cicek）。其中用到了这个正则表达式去除转换后产生的特殊字符。
select upper(decode(instr(convert (replace(u.USERS_SYS_LASTNAME, '?'), 'us7ascii'), '?')
, 0, trim(regexp_replace(convert (u.USERS_SYS_LASTNAME, 'us7ascii'), '[^[:alnum:]]'))
, u.USERS_SYS_LASTNAME
)
|| ', ' ||
decode(instr(convert (replace(u.USERS_SYS_FIRSTNAME, '?'), 'us7ascii'), '?')
, 0, trim(regexp_replace(convert (u.USERS_SYS_FIRSTNAME, 'us7ascii'), '[^[:alnum:]]'))
, u.USERS_SYS_FIRSTNAME
)
) as "Name"
from change_on_setup.USERS_SYSINFO u
另一种转换方法：
select regexp_replace (convert (users_sys_firstname||', '||users_sys_lastname, 'us7ascii'), '[[:punct:]]') as "Name"
from change_on_setup.USERS_SYSINFO u
4. 将字符串拆分
正则表达式：
[^,]+
[^,]*
用法：
select TRIM(REGEXP_SUBSTR('cgrant,mhoff,vstokes','[^,]+', 1,level)) POS_CODE
from dual
connect by level <= length(REGEXP_REPLACE('cgrant,mhoff,vstokes','[^,]*'))+1
输出结果：
USERNAME
--------
cgrant
mhoff
vstokes
更多的Oracle正则表达式可以参见这个网址（Alex推荐）
http://regexlib.com/
另外还有一个可以自动生成Oracle 10g正则表达式的工具——RegexBuddy。
官方网站：http://www.regexbuddy.com/oracle.html
