Oracle--通配符、Escape转义字符、模糊查询语句-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

Oracle--通配符、Escape转义字符、模糊查询语句

简介:

转自原文Oracle--通配符、Escape转义字符、模糊查询语句

 

一、通配符

通配符描述示例
     %:匹配包含零个或更多字符的任意字符串。WHERE title LIKE '%computer%' 将查找处于书名任意位置的包含单词 computer 的所有书名。
      _(下划线):匹配任何单个字符。WHERE au_fname LIKE '_ean' 将查找以 ean 结尾的所有 4 个字母的名字(Dean、Sean 等)。
      [ ]:指定范围 ,([a-f]) 或集合 ([abcdef]) 中的任何单个字符。WHERE au_lname LIKE '[C-P]arsen' 将查找以arsen 结尾且以介于 C 与 P 之间的任何单个字符开始的作者姓氏,例如,Carsen、Larsen、Karsen 等。
      [^]:不属于指定范围, ([a-f]) 或集合 ([abcdef]) 的任何单个字符。WHERE au_lname LIKE 'de[^l]%' 将查找以 de 开始且其后的字母不为 l 的所有作者的姓氏。

1、LIKE'Mc%' 将搜索以字母 Mc 开头的所有字符串(如 McBadden)。
2、LIKE'%inger' 将搜索以字母 inger 结尾的所有字符串(如 Ringer、Stringer)。
3、LIKE'%en%' 将搜索在任何位置包含字母 en 的所有字符串(如 Bennet、Green、McBadden)。
4、LIKE'_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称(如 Cheryl、Sheryl)。
5、LIKE'[CK]ars[eo]n' 将搜索下列字符串:Carsen、Karsen、Carson 和 Karson(如 Carson)。
6、LIKE'[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称(如 Ringer)。
7、LIKE'M[^c]%' 将搜索以字母 M 开头,并且第二个字母不是 c 的所有名称(如MacFeather)。

二、Escape 转义字符

用户输入如果没有任何限制的话,则必须对特殊字符进行变换。
例如:如果对单引号不进行变换,则会发生数据库错误,甚至可能导致系统崩溃。
不过回避方法却非常简单,只要将单引号[']转换成两个单引号['']就可以了。
 
例:SELECT * FROM TBL WHERE COL = 'ABC''DEF'; 
 模糊查询的语句虽然不会发生SQL错误,但是不进行回避的话,则无法得到要检索的值。
 回避方法较单引号复杂。需要使用转义符。将[%]转为[/%]、[_]转为[/_],然后再加上[ESCAPE '/']就可以了。 
    例:SELECT * FROM TBL WHERE COL LIKE 'ABC/%/_%' ESCAPE '/'; 
  ※最后一个%是通配符。
  
如果做日文项目的话,会出现全角字符的[%]、[_],而这两个全角字符同样会作为半角通配符处理。所以在变换时,同时需要将全角的[%]、[_]进行变换。 
例:SELECT * FROM TBL WHERE COL LIKE 'ABC/%/_/%/_%' ESCAPE '/'; 
变换成这样似乎结束了,可是不要忘了还有转义符自身,万一用户输入转义符的话,
以上的处理就会发生SQL错误。所以也必须对转义符进行变换。变换方法就是将[/]转换为[//]。
例:SELECT * FROM TBL WHERE COL LIKE 'ABC/%/_/%///_%' ESCAPE '/'; 
以上的操作都针对于一般的数据类型,如CHAR、VARCHAR2。
 如果出现NCHAR、NVARCHAR2的话,以上的处理就会出现ORA-01425错误。

 
如果改成以下写法,则会发生ORA-01424错误。 
    SELECT * FROM TBL WHERE COL LIKE '%/_%' ESCAPE TO_NCHAR('/') 
正确的写法应该是 
    SELECT * FROM TBL WHERE COL LIKEC '%/_%' ESCAPE TO_NCHAR('/') 

三、模糊查询语句

CREATE TABLE testsql (test_data VARCHAR2(15) NOT NULL); 

SELECT * FROM testsql ;  

 


其中包含特殊的字符分别为%,_,&,有可能包含这些字符的数据含有错误,或者需要查找包含这些字符的数据。
例1、4种模糊查询查下划线
1、SQL> SELECT * FROM testsql WHERE test_data LIKE 'sdd_%' ESCAPE ' ';

     其中: 转义字符为' '(空一格);
2、SQL> SELECT * FROM testsql WHERE test_data LIKE 'sdd/_%' ESCAPE '/';

    其中:转义字符为'/';

3、SQL> SELECT * FROM testsql WHERE test_data LIKE 'sdd=_%' escape '=';

   其中: 转义字符为'=';
4、SQL> SELECT * FROM testsql WHERE test_data LIKE 'sddd_%' escape 'd';
未选定行
        转义字符为d,没有实现转义功能;

同理:通过这种方法查找含有'%'的所有字段:
SQL> SELECT * FROM testsql WHERE test_data LIKE '%/%%' escape '/';
 

例2、 但是'&'不能通过转义字符查找:
SQL> SELECT * FROM testsql WHERE test_data LIKE '%/&%' escape'/';                             *
   

        可以通过另外的方式进行转义:
SQL> SELECT ASCII('&') FROM dual;
  
SQL> SELECT * FROM testsql WHERE test_data LIKE '%'||chr(38)||'%';
        

Oracle 使用“||”进行字符串连接

 例3、 '的转义:
SQL> SELECT * FROM testsql WHERE test_data LIKE '%''%';

例4、特殊符号“&”的插入

方法一、SQL> INSERT INTO testInsert VALUES('test'||chr(38)||'');


方法二、SQL>INSERT INTO testInsert VALUES('Tom' || '&' || 'Jerry' )


例5、特殊符号单引号的插入

方法一、SQL>INSERT INTO testInsert VALUES('test' || '''' );


注意:这里的''''四个单引号是什么意思呢?首先第一个和最后一个都是Oracle中的字符串连接符,这个没有异议。那么第二个'和第三'又表示什么意思呢?第二个'是一个转义字符。第三个'才是我们真正的内容

方法二、SQL>INSERT INTO testInsert VALUES('test''' );

方法三、SQL>INSERT INTO testInsert VALUES('test'|| chr(39)||'');

 

没有整理与归纳的知识,一文不值!高度概括与梳理的知识,才是自己真正的知识与技能。 永远不要让自己的自由、好奇、充满创造力的想法被现实的框架所束缚,让创造力自由成长吧! 多花时间,关心他(她)人,正如别人所关心你的。理想的腾飞与实现,没有别人的支持与帮助,是万万不能的。







    本文转自wenglabs博客园博客,原文链接:http://www.cnblogs.com/arxive/p/6427721.html,如需转载请自行联系原作者




版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章