首页 > 代码库 > Oracle中使用escape关键字实现like匹配特殊字符,以及&字符的转义
Oracle中使用escape关键字实现like匹配特殊字符,以及&字符的转义
转:http://blog.chinaunix.net/uid-26896647-id-3433968.html
问题描述:如果在一个表中的一个字段上存在‘&‘, ‘_‘, ‘%‘这样的特殊字符,而我们又得在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)去替代‘&‘!
来自为知笔记(Wiz)
Oracle中使用escape关键字实现like匹配特殊字符,以及&字符的转义
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。