ORACLE like 下划线 模糊查询 escape

ORACLE中ESCAPE关键字用法
原文出自【比特网】，转载请保留原文链接：http://soft.chinabyte.com/database/398/12429898.shtml

　定义：escape关键字经常用于使某些特殊字符，如通配符：'%','_'转义为它们原来的字符的意义，被定义的转义字符通常使用'\',但是也可以使用其他的符号。
 
　　实例：
 
　　SQL> select * from t11 where name like '%_%';
 
　　NAME
 
　　---------
 
　　aa_a
 
　　aaa
 
　　SQL> select * from t11 where name like '%\_%' escape '\';
 
　　NAME
 
　　----------
 
　　aa_a
 
　　注意：如果是 '/' 作为检索字符， 必须 用 '/' 作为转义符， 正斜扛也一样。
 
　　select * from wan_test where psid like '%//%' escape '/'
 
　　1.使用 ESCAPE 关键字定义转义符。在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。
 
　　2.ESCAPE 'escape_character' 允许在字符串中搜索通配符而不是将其作为通配符使用。escape_character 是放在通配符前表示此特殊用途的字符。
 
　　select * from a WHERE name LIKE '%/%ab' ESCAPE '/'
 
　　结果为：
 
　　name
 
　　----------
 
　　11%ab
 
　　12%ab
 
　　==================================================================================
 
　　SQL> select * from test;
 
　　TEST
 
　　--------------------
 
　　sdd_kk
 
　　d'd
 
　　dfsfsa
 
　　dffa%asfs
 
　　12345
 
　　1%2345
 
　　1%54321
 
　　2%54321
 
　　%%54321
 
　　A&B
 
　　已选择9行。
 
　　其中包含特殊的字符分别为%,_,&，有可能包含这些字符的数据含有错误，或者需要查找包含这些字符的数据。
 
　　SQL> select * from test where test like 'sdd _%' escape ' ';
 
　　TEST
 
　　--------------------
 
　　sdd_kk
 
　　转义字符为' '(空格);
 
　　SQL> select * from test where test like 'sdd\_%' escape '\';
 
　　TEST
 
　　--------------------
 
　　sdd_kk
 
　　转义字符为'\';
 
　　SQL> select * from test where test like 'sdd=_%' escape '=';
 
　　TEST
 
　　--------------------
 
　　sdd_kk
 
　　转义字符为'=';
 
　　SQL> select * from test where test like 'sdd/_%' escape '/';
 
　　TEST
 
　　--------------------
 
　　sdd_kk 转义字符为'/';
 
　　SQL> select * from test where test like 'sddd_%' escape 'd';
 
　　未选定行
 
　　转义字符为d，没有实现转义功能;
 
　　SQL> select * from test where test like '%\_%' escape '\';
 
　　TEST
 
　　--------------------
 
　　sdd_kk
 
　　查找包含所有'_'的字段。
 
　　同理：通过这种方法查找含有'%'的所有字段：
 
　　SQL> select * from test where test like '%\%%' escape '\';
 
　　TEST
 
　　--------------------
 
　　dffa%asfs
 
　　1%2345
 
　　1%54321
 
　　2%54321
 
　　%%54321
 
　　但是'&'不能通过转义字符查找：
 
　　SQL> select * from test where test like '%\&%' escape'\';
 
　　select * from test where test like '%\&%' escape'\'
 
　　*
 
　　第 1 行出现错误：
 
　　ORA-01424: 转义符之后字符缺失或非法
 
　　可以通过另外的方式进行转义：
 
　　SQL> select ascii('&') from dual;
 
　　ASCII('&')
 
　　----------
 
　　38
 
　　SQL> select * from test where test like '%'||chr(38)||'%';
 
　　TEST
 
　　--------------------
 
　　A&B
 
　　'''的转义：
 
　　SQL> select * from test where test like '%''%';
 
　　TEST
 
　　--------------------
 
　　d'd
 
　　特殊符号的数据的插入
 
　　SQL> insert into test values('test&test');
 
　　输入 test 的值： test
 
　　原值 1: insert into test values('test&test')
 
　　新值 1: insert into test values('testtest') -虽然插入，但是数据不对。
 
　　已创建 1 行。
 
　　SQL> show define
 
　　define "&" (hex 26)
 
　　SQL> set define off
 
　　SQL> show define
 
　　define OFF
 
　　SQL> insert into test values('test&test');
 
　　已创建 1 行。
 
　　SQL> show escape
 
　　escape OFF
 
　　SQL> set escape on
 
　　SQL> show escape
 
　　escape "\" (hex 5c)
 
　　SQL> insert into test values('test\&test');
 
　　已创建 1 行。
 
　　SQL> select * from test;
 
　　TEST
 
　　--------------------
 
　　sdd_kk
 
　　d'd
 
　　dfsfsa
 
　　dffa%asfs
 
　　12345
 
　　1%2345
 
　　1%54321
 
　　2%54321
 
　　%%54321
 
　　A&B
 
　　testtest
 
　　test&test
 
　　test&test
 
　　已选择13行。
 
　　SQL> commit;
 
　　提交完成。
 
　　SQL> select * from test;
 
　　TEST
 
　　--------------------
 
　　sdd_kk
 
　　d'd
 
　　dfsfsa
 
　　dffa%asfs
 
　　12345
 
　　1%2345
 
　　1%54321
 
　　2%54321
 
　　%%54321
 
　　A&B
 
　　testtest
 
　　test&test
 
　　test&test
 
　　已选择13行。
 
　　SQL> insert into test values('test\%test');
 
　　已创建 1 行。
 
　　SQL> insert into test values('test\_test');
 
　　已创建 1 行。
 
　　SQL> insert into test values('test\'test);
 
　　insert into test values('test'test)
 
　　*
 
　　第 1 行出现错误：
 
　　ORA-00917: 缺失逗号
 
　　SQL> insert into test values('test''test');
 
　　已创建 1 行。
 
　　SQL> select * from test;
 
　　TEST
 
　　--------------------
 
　　sdd_kk
 
　　d'd
 
　　dfsfsa
 
　　dffa%asfs
 
　　12345
 
　　1%2345
 
　　1%54321
 
　　2%54321
 
　　%%54321
 
　　A&B
 
　　testtest
 
　　test&test
 
　　test&test
 
　　test%test
 
　　test_test
 
　　test'test
 
　　已选择16行。
 
　　连接符‘||’导致了新一轮的转义：连接符号‘||’左右的单引号没有任何的关系，除非‘||’是作为字符串的一部分(这在动态SQL中很常见)。
 
　　SQL> SELECT 'ORACLE'||'''' FROM DUAL;
 
　　'ORACLE'||''
 
　　----------
 
　　ORACLE'
 
　　个人理解，'ORACLE'||'''' 后面的“''''”应该认为是一个字符串(即前后单引号，中间是“''”串，而中间又是密集单引号，因此第一个为转义功能)
 
　　SQL> SELECT 'ORACLE''''' FROM DUAL;
 
　　'ORACLE'''''
 
　　------------
 
　　ORACLE''
 
　　对于第一个，前两个单引号配对，后面四个单引号按照上面的第一条原则分配，既：SELECT 'ORACLE'||'''' FROM DUAL;
 
　　对于第二个，由于第二个单引号后面存在单引号，所以就不与第一个配对，而是充当了转义的角色。既：SELECT 'ORACLE''''' FROM DUAL;

原文出自【比特网】，转载请保留原文链接：http://soft.chinabyte.com/database/398/12429898.shtml