简单的方式,使用“%” 和 “_”
字符匹配操作可以使用通配符 “%” 和 “_”:
%:表示任意个字符,包括零个;
_:表示一个任意字符;
Connected to aspx?tid=12” target=”_blank” title=”Oracle”>Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> select * from dept;
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
“%” 和 “_”演示:
SQL> select * from dept where DNAME like ‘_A%’;
DEPTNO DNAME LOC
30 SALES CHICAGO
ESCAPE 演示:
SQL> insert into dept values(50,’BEIJING’,’JIANG%XI’);
1 row inserted
SQL> select * from dept;
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 BEIJING JIANG%XI
SQL> select * from dept where loc like ‘%\%%’ escape ‘\’;
DEPTNO DNAME LOC
50 BEIJING JIANG%XI
SQL> select * from dept where loc like ‘%e%%’ escape ‘e’;
DEPTNO DNAME LOC
50 BEIJING JIANG%XI
复杂的方式
oracle在Where子句中,可以对datetime、char、varchar字段类型的列用Like子句配合通配符选取那些“很像…”的数据记录,以下是可使用的通配符:
% 零或者多个字符
_ 单一任何字符(下划线)
\ 特殊字符
oracle10g以上支持正则表达式的函数主要有下面四个:
1,REGEXP_LIKE :与LIKE的功能相似
2,REGEXP_INSTR :与INSTR的功能相似
3,REGEXP_SUBSTR :与SUBSTR的功能相似
4,REGEXP_REPLACE :与REPLACE的功能相似
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 位置和顺序
|
*/
其中关于条件,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为三个字且第一个字是“三”的;
- regexp_like 正则表达式函数查询
–查询FieldName中以1开头60结束的记录并且长度是7位
select * from fzq where FieldName like ‘1____60’;
select * from fzq where regexp_like(FieldName,’1….60’);
–查询FieldName中以1开头60结束的记录并且长度是7位并且全部是数字的记录。
–使用like就不是很好实现了。
select * from fzq where regexp_like(FieldName,’1[0-9]{4}60’);
– 也可以这样实现,使用字符集。
select * from fzq where regexp_like(FieldName,’1[[:digit:]]{4}60’);
– 查询FieldName中不是纯数字的记录
select * from fzq where not regexp_like(FieldName,’^[[:digit:]]+’);
– 查询FieldName中不包含任何数字的记录。
select * from fzq where regexp_like(FieldName,’^[^[:digit:]]+’);
–查询以12或者1b开头的记录.不区分大小写。
select * from fzq where regexp_like(FieldName,’^1[2b]’,’i’);
–查询以12或者1b开头的记录.区分大小写。
select * from fzq where regexp_like(FieldName,’^1[2B]’);
– 查询数据中包含空白的记录。
select * from fzq where regexp_like(FieldName,’[[:space:]]’);
–查询所有包含小写字母或者数字的记录。
select * from fzq where regexp_like(FieldName,’^([a-z]+|[0-9]+)$’);
–查询任何包含标点符号的记录。
select * from fzq where regexp_like(FieldName,’[[:punct:]]’);
escape关键字实现like匹配特殊字符,以及&字符的转义
问题描述:如果在一个表中的一个字段上存在’&’, ‘_’, ‘%’这样的特殊字符,而我们又得在where条件中使用到这些特殊字符怎么办?
1.创建含有这些特殊字符的表
SQL> create table t_test_escape(name varchar2(20));
Table created
SQL> insert into t_test_escape(name) values(‘&_hello’);
1 row inserted
–这里会提示我输入变量_hello的值,我没有输入任何值,所以为空!
SQL> select * from t_test_escape;
NAME
结果自然也为空
SQL> truncate table t_test_escape;
Table truncated
–使用set define off 关闭替代变量的功能
SQL> set define off
SQL> insert into t_test_escape(name) values(‘&_hello’);
1 row inserted
SQL> insert into t_test_escape(name) values(‘%%_hello’);
1 row inserted
SQL> insert into t_test_escape(name) values(‘Oracle%&_hello’);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t_test_escape;
NAME
&_hello
%%_hello
Oracle%&_hello
–使用set define off关闭变量替换功能之后,果然能够插入含有&的特殊字符了。
2.使用escape关键字在模糊查询中查看字符中含有%的字符串
SQL> select * from t_test_escape where name like ‘%a%%’ escape ‘a’;
NAME
%%_hello
Oracle%&_hello
–上面使用的转义字符为’a’
3.使用escape关键字模糊查询含有’&’的字符串
因为此时还是set define off的 所以这个时候&并不是什么特殊字符,所以下面的查询会报错
SQL> select * from t_test_escape where name like ‘%a&%’ escape ‘a’;
select * from t_test_escape where name like ‘%a&%’ escape ‘a’
ORA-01424: missing or illegal character following the escape character
SQL> insert into t_test_escape(name) values(‘Oracle%&hello’);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t_test_escape where name like ‘%a&h%’ escape ‘a’;
select * from t_test_escape where name like ‘%a&h%’ escape ‘a’
ORA-01424: missing or illegal character following the escape character
在set define off关闭替代变量功能之后可以直接将&当做普通字符,而不用escape
SQL> select * from t_test_escape where name like ‘%&h%’;
NAME
Oracle%&hello
使用set define on打开替代变量功能
SQL> set define on;
SQL> select * from t_test_escape where name like ‘%&h%’;
–这里会提示我输入变量h的值,因为我没有输入任何值,这条sql条件相当于就是like ‘%%’,所以返回全部数据
NAME
&_hello
%%_hello
Oracle%&_hello
Oracle%&hello
–使用escape关键字指定特定的转义字符试试看
SQL> select * from t_test_escape where name like ‘%a&h%’ escape ‘a’;
NAME
还是会提示我输入变量h的值
–下面通过查询出’&’的ascii来绕过这个障碍
SQL> select ascii(‘&’) from dual;
ASCII(‘&’)
38
–使用chr(38)去替代特殊字符’&’
SQL> select * from t_test_escape where name like ‘%’ || chr(38) || ‘h%’;
NAME
Oracle%&hello
4.使用escape关键字模糊查询含有’&’的字符串
SQL> select * from t_test_escape where name like ‘%a_%’ escape ‘a’;
NAME
&_hello
%%_hello
Oracle%&_hello
5.下面我将替代变量的特殊字符改为试试,然后看能不能使用模糊匹配匹配特殊字符’&’
SQL> set define
SQL> select * from t_test_escape where name like ‘%&h%’;
NAME
Oracle%&hello
总结:对于使用escape关键字去转义特殊字符的时候,并不是对于所有的特殊字符都能够转义成功,上面的实验表明,对于’%’, ‘‘, ‘&’来说,使用escape是能够成功转义’%’, ‘‘的,但是却不能转义’&’,当然这只是默认的情况,如果我们将绑定变量的标识符设置为非默认的$,那么我们就可以把’&’当做普通字符对待了。如果不改变绑定变量的默认标识符,那么就使用chr(38)去替代’&’!