ORACLE中ESCAPE关键字用法

 

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