ORACLE中ESCAPE关键字用法

转载

赶路人儿 2022-06-15 17:08:49 博主文章分类：数据库-oracle

定义：escape关键字经常用于使某些特殊字符，如通配符：'%','_'转义为它们原来的字符的意义，被定义的转义字符通常使用'\',但是也可以使用其他的符号。

实例： SQL> select * from t11 where name like '%_%'; www.2cto.com 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 www.2cto.com ================================================================================== 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 www.2cto.com 转义字符为' '(空格); 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 www.2cto.com 转义字符为'/'; SQL> select * from test where test like 'sddd_%' escape 'd'; 未选定行转义字符为d，没有实现转义功能； SQL> select * from test where test like '%\_%' escape '\'; TEST -------------------- sdd_kk www.2cto.com 查找包含所有'_'的字段。同理：通过这种方法查找含有'%'的所有字段： 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'\' www.2cto.com * 第 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 行。 www.2cto.com 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行。 www.2cto.com 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: 缺失逗号 www.2cto.com 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; www.2cto.com 'ORACLE''''' ------------ ORACLE'' 对于第一个，前两个单引号配对，后面四个单引号按照上面的第一条原则分配，既：SELECT 'ORACLE'||'''' FROM DUAL; 对于第二个，由于第二个单引号后面存在单引号，所以就不与第一个配对，而是充当了转义的角色。既：SELECT 'ORACLE''''' FROM DUAL;