oracle 正则表达式

た入场券 2022-04-21 15:56 271阅读 0赞

ORACLE中的支持正则表达式的函数主要有下面四个：  
1，REGEXP\_LIKE ：与LIKE的功能相似  
2，REGEXP\_INSTR ：与INSTR的功能相似  
3，REGEXP\_SUBSTR ：与SUBSTR的功能相似  
4，REGEXP\_REPLACE ：与REPLACE的功能相似

**正则表达式中常用到的元数据(metacharacter)如下：**

^ 匹配字符串的开头位置。

$ 匹配支付传的结尾位置。

\*匹配该字符前面的一个字符0次，1次或者多次出现。例如52\*oracle 可以匹配 5oracle,52oracle,522oracle,5222oracle等等。

\+ 匹配该字符前面的一个字符1次或者多次出现。例如52+oracle 可以匹配 52oracle,522oracle,5222oracle等等

? 匹配该字符前面的一个字符0次或1次或者多次出现。例如52?oracle 只能匹配5oracle,52oracle等等

\{n\} 匹配一个字符串n次，n为正整数。例如：hel\{2\}o 所匹配的是hello

\{n,m\} 匹配一个字符串至少n次，至多m次。其中n和m都是整数。

. 匹配除了null之外的任何单个字符串

(pattern) 这个是用来匹配指定模式的一个子表达式

x|y 匹配x或者y，其中x和y是一个或者多个字符

\[abc\] 匹配括号中的任意一个字符。例如：\[ab\]bc可以匹配abc和bbc

\[a-z\] 匹配指定范围内的任意字符串。例如\[A-G\]hi可以匹配Ahi至Ghi

\[::\]指定一个字符类，可以匹配该类中的任意字符 这里的字符类包括：

\[:alphanum:\] 可以匹配字符0-9、A-Z、a-z

\[:alpha:\]可以匹配字符A-Z、a-z

\[:blank:\]可以匹配空格或者tab键

\[:digit:\]可以匹配数字 0-9

\[:gragh:\]可以匹配非空字符

\[:punct:\]可以匹配. , ” ‘等标点符号。

\[:upper:\]可以匹配字符A-Z

\[:lower:\]可以匹配字符a-z

各种操作符的运算优先级  
\\转义符  
(), (?:), (?=), \[\] 圆括号和方括号  
\*, +, ?, \{n\}, \{n,\}, \{n,m\} 限定符  
^, $, anymetacharacter 位置和顺序  
|  
\*/

字符串’^198\[0-9\]$’可以匹配‘1980-1989’，如果希望统计出公司那些员工是80年～89年入职的，就可以使用如下的SQL语句：

select \* from emp e whereregexp\_like(to\_char( e.hiredate,'yyyy'),'^198\[0-9\]$');

substr和instr套嵌使用就可以实现indexof类似功能

eg：园林专业优先电话:07713212335-803 取电话号码，通过 '电话:' 做为分隔。

select substr('园林专业优先电话:07713212335-803',instr('园林专业优先电话:07713212335-803','电话:')+3,20) from dual;

\--查询@TV 后面是否全部都是数字  
select t.acc\_nbr,t.main\_acc\_nbr from user t  
where regexp\_like(substr(t.main\_acc\_nbr,instr(UPPER(t.main\_acc\_nbr),'@TV')+3,20),'^\[0-9\]+\[0-9\]\*$')  
and t.acc\_nbr='pay001';  
  
\--查询@TV 后面是否全部都是数字  
select t.acc\_nbr,t.main\_acc\_nbr from user t  
where regexp\_like(substr(t.main\_acc\_nbr,instr(UPPER(t.main\_acc\_nbr),'@TV')+3,20),'^\[\[:digit:\]\]+$')  
and t.acc\_nbr='pay001';

\--查询@TV 后面是否全部都是数字  
select t.main\_acc\_nbr from user t  
\--排除没有@TV的账号  
where to\_number(instr(t.main\_acc\_nbr,'@TV')) != 0  
\--排除账号@TV 后面是否全部都是数字  
and regexp\_like(substr(t.main\_acc\_nbr,instr(UPPER(t.main\_acc\_nbr),'@TV')+3,20),'^\[0-9\]+\[0-9\]\*$') ;

参考：

**oracle 字符函数相关**http://wenku.baidu.com/view/5a76e0c58bd63186bcebbcb1.html

## Oracle中INSTR和SUBSTR的用法 ##

http://blog.163.com/liu\_yang1234/blog/static/2447431020112290109559/

http://blog.sina.com.cn/s/blog\_53636a020100f93i.html

正则表达式用于字符串处理、表单验证等场合，实用高效。现将一些常用的表达式收集于此，以备不时之需。

用户名：/^\[a-z0-9\_-\]\{3,16\}$/

密码：/^\[a-z0-9\_-\]\{6,18\}$/

十六进制值：/^\#?(\[a-f0-9\]\{6\}|\[a-f0-9\]\{3\})$/

电子邮箱：/^(\[a-z0-9\_\\.-\]+)@(\[\\da-z\\.-\]+)\\.(\[a-z\\.\]\{2,6\})$/

URL：/^(https?:\\/\\/)?(\[\\da-z\\.-\]+)\\.(\[a-z\\.\]\{2,6\})(\[\\/\\w \\.-\]\*)\*\\/?$/

IP 地址：/^(?:(?:25\[0-5\]|2\[0-4\]\[0-9\]|\[01\]?\[0-9\]\[0-9\]?)\\.)\{3\}(?:25\[0-5\]|2\[0-4\]\[0-9\]|\[01\]?\[0-9\]\[0-9\]?)$/

HTML 标签：/^<(\[a-z\]+)(\[^<\]+)\*(?:>(.\*)<\\/\\1>|\\s+\\/>)$/

Unicode编码中的汉字范围：/^\[u4e00-u9fa5\],\{0,\}$/

匹配中文字符的正则表达式： \[\\u4e00-\\u9fa5\]  
评注：匹配中文还真是个头疼的事，有了这个表达式就好办了

匹配双字节字符(包括汉字在内)：\[^\\x00-\\xff\]  
评注：可以用来计算字符串的长度（一个双字节字符长度计2，ASCII字符计1）

匹配空白行的正则表达式：\\n\\s\*\\r  
评注：可以用来删除空白行

匹配HTML标记的正则表达式：<(\\S\*?)\[^>\]\*>.\*?</\\1>|<.\*? />  
评注：网上流传的版本太糟糕，上面这个也仅仅能匹配部分，对于复杂的嵌套标记依旧无能为力

匹配首尾空白字符的正则表达式：^\\s\*|\\s\*$  
评注：可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式

匹配Email地址的正则表达式：\\w+(\[-+.\]\\w+)\*@\\w+(\[-.\]\\w+)\*\\.\\w+(\[-.\]\\w+)\*  
评注：表单验证时很实用

匹配网址URL的正则表达式：\[a-zA-z\]+://\[^\\s\]\*  
评注：网上流传的版本功能很有限，上面这个基本可以满足需求

匹配帐号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：^\[a-zA-Z\]\[a-zA-Z0-9\_\]\{4,15\}$  
评注：表单验证时很实用

匹配国内电话号码：\\d\{3\}-\\d\{8\}|\\d\{4\}-\\d\{7\}  
评注：匹配形式如 0511-4405222 或 021-87888822

匹配腾讯QQ号：\[1-9\]\[0-9\]\{4,\}  
评注：腾讯QQ号从10000开始

匹配中国大陆邮政编码：\[1-9\]\\d\{5\}(?!\\d)  
评注：中国大陆邮政编码为6位数字

匹配身份证：\\d\{15\}|\\d\{18\}  
评注：中国大陆的身份证为15位或18位

匹配ip地址：\\d+\\.\\d+\\.\\d+\\.\\d+  
评注：提取ip地址时有用

匹配特定数字：  
^\[1-9\]\\d\*$　 //匹配正整数  
^-\[1-9\]\\d\*$//匹配负整数  
^-?\[1-9\]\\d\*$//匹配整数  
^\[1-9\]\\d\*|0$//匹配非负整数（正整数 + 0）  
^-\[1-9\]\\d\*|0$//匹配非正整数（负整数 + 0）  
^\[1-9\]\\d\*\\.\\d\*|0\\.\\d\*\[1-9\]\\d\*$//匹配正浮点数  
^-(\[1-9\]\\d\*\\.\\d\*|0\\.\\d\*\[1-9\]\\d\*)$//匹配负浮点数  
^-?(\[1-9\]\\d\*\\.\\d\*|0\\.\\d\*\[1-9\]\\d\*|0?\\.0+|0)$//匹配浮点数  
^\[1-9\]\\d\*\\.\\d\*|0\\.\\d\*\[1-9\]\\d\*|0?\\.0+|0$//匹配非负浮点数（正浮点数 + 0）  
^(-(\[1-9\]\\d\*\\.\\d\*|0\\.\\d\*\[1-9\]\\d\*))|0?\\.0+|0$ //匹配非正浮点数（负浮点数 + 0）  
评注：处理大量数据时有用，具体应用时注意修正

匹配特定字符串：  
^\[A-Za-z\]+$ //匹配由26个英文字母组成的字符串  
^\[A-Z\]+$ //匹配由26个英文字母的大写组成的字符串  
^\[a-z\]+$ //匹配由26个英文字母的小写组成的字符串  
^\[A-Za-z0-9\]+$ //匹配由数字和26个英文字母组成的字符串  
^\\w+$ //匹配由数字、26个英文字母或者下划线组成的字符串

去除精度纬度中非浮点数

select jd,wd from xx\_xa\_xsczaqpc t where t.id not in(

select id from xx\_xa\_xsczaqpc where regexp\_like(jd,'^\[1-9\]\\d\*\\.\\d\*|0\\.\\d\*\[1-9\]\\d\*$')) ;

是双小数点的数

select xx\_jbxx\_id,jd,wd from xx\_xa\_xsczaqpc t where regexp\_like(jd,'^\[1-9\]\\d\*\\.\\d\*\\.\\d\*\[1-9\]\\d\*$')