oracle 通配符 转义

 

　　定义：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