Oracle中like与regexp_like的用法

相信大家在工作中多少会遇到对模糊数据的查询，下面是我在实际工作遇到的问题，然后进行了总结，希望对大家有所帮助！

REGEXP：
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）所构成：
'^' 匹配输入字符串的开始位置，（注意，若是在方括号表达式中使用^，此时它表示不接受该字符集合，如[^[:digit:]]，不是数字）。
'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 '\n' 或 '\r'。
'.' 匹配除换行符之外的任何单字符。
'?' 匹配前面的子表达式零次或一次。
'+' 匹配前面的子表达式一次或多次。
'*' 匹配前面的子表达式零次或多次。
'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。
'( )' 标记一个子表达式的开始和结束位置。
'[]' 标记一个中括号表达式。
'{m,n}' 一个精确地出现次数范围，m= 出现m次。
\num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。
字符簇： 
[[:alpha:]] 任何字母。
[[:digit:]] 任何数字。
[[:alnum:]] 任何字母和数字。
[[:space:]] 任何白字符。
[[:upper:]] 任何大写字母。
[[:lower:]] 任何小写字母。
[[:punct:]] 任何标点符号。
[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。
各种操作符的运算优先级
\转义符
(), (?:), (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和顺序

一、REGEXP_LIKE在工作中的用法
说明：公司oracle数据库中有个合同表：agreement ，其中有个字段名称为：端口号（long_num），字段类型为：varchar2(30)，具体表中的部分数据如下：



现在需要将端口号中的不带结尾带”_"的数据提取出来，由于"_"在like中为通配符，所以想到了regexp_like
执行语句：

select 
a.agreement_id 合同编号,
a.name 合同名称,
e.customer_id 客户编号,
e.customer_name 客户名称,
a.start_date 合同开始时间,
a.end_date 合同结束时间,
a.status 合同状态,
a.product_id 产品编号,
a.long_num 端口号,
a.create_time 合同创建时间,
a.service_charge_id 套餐编号,
a.service_code 服务编号,
a.channel 通道
from agreement a,enterprise_customer e where a.customer_id=e.customer_id and e.province_code='09' 
and not regexp_like (a.long_num,'[0-9]_')

PS：如果取反向可以去掉 not

二、LIKE的用法：

在where子句中，可以对datetime、char、varchar字段类型的列用like子句配合通配符选取那些"很像…"的数据记录，以下是可使用的通配符： 
“%”零或者多个字符
“_”单一任何字符（下划线）
“\”特殊字符
“[]”在某一范围内的字符，如[0-9]或者[aeth]
“[^]”不在某范围内的字符，如[^0-9]或者[^aeth]
 
其中关于条件，sql提供了四种匹配模式：

1，%:表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

如：
select * from [user] where u_name like '%三%'
 
将会把u_name为"张三","张猫三"、"三脚猫","唐三藏"等等有"三"的记录全找出来。
另外，如果需要找出u_name中既有"三"又有"猫"的记录，请使用and条件

如：
select * from [user] where u_name like '%三%' and u_name like '%猫%'

若使用select * from [user] where u_name like '%三%猫%'虽然能搜索出"三脚猫",但不能搜索出符合条件的"张猫三".
 
2，_: 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：

如：
select * from [user] where u_name like '_三_'
 
只找出"唐三藏"这样u_name为三个字且中间一个字是"三"的；

如:
select * from [user] where u_name like '三__';
 
只找出"三脚猫"这样name为三个字且第一个字是"三"的；
  
3，[ ]:表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

如:
select * from [user] where u_name like '[张李王]三'
 
将找出"张三"、"李三"、"王三"（而不是"张李王三"）；
如果[ ]内有一系列字符（01234、abcde之类的）则可略写为"0-4"、"a-e"
 
如:
select * from [user] where u_name like '老[1-9]'
 
将找出"老1"、"老2"、……、"老9";

4，[^ ]:表示不在括号所列之内的单个字符。其取值和[] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

如：
select * from [user] where u_name like '[^张李王]三'
 
将找出不姓"张"、"李"、"王"的"赵三"、"孙三"等；
 
如:
select * from [user] where u_name like '老[^1-4]';
 
将排除"老1"到"老4",寻找"老5"、"老6"、…… 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27064835/viewspace-1623522/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27064835/viewspace-1623522/