Oracle特殊字符插入与查询

查询字段中特殊字段转义

---- 特殊字符指的是包含“%  _  &”的字符

1.环境准备

create table shall(test varchar2(100));

insert into shall values('zhong _zz');

insert into shall values('zhong ''zz');

insert into shall values('zhong %zz');

insert into shall values('zhong %%%zz');

insert into shall values('zhongzz');

insert into shall values('zhong "&"zz');

commit;

 

SQL> select * from shall;

TEST

--------------------------------------------------------------------------------

zhong _zz

zhong 'zz

zhong %zz

zhong %%%zz

zhongzz

zhong "&"zz

 

 

2.特殊字符插入

SQL> show define

define "&" (hex 26)

SQL> set define off

SQL> insert into shall values('shall &zhong');

SQL> show define

define OFF

SQL> set define on

SQL> show define

define "&" (hex 26)

 

SQL> show escape

escape OFF

SQL> set escape on

SQL> show escape

escape "\" (hex 5c)

SQL> insert into shall values('shall \&zhong2');

SQL> set escape off

SQL> show escape

escape OFF

SQL> commit;

 

SQL> select * from shall;

TEST

--------------------------------------------------------------------------------

zhong _zz

zhong 'zz

zhong %zz

zhong %%%zz

zhongzz

zhong "&"zz

shall &zhong

shall &zhong2

 

 

3.查询特殊字符的字段 escape转义

 

--------注意转义字符不能在查询中出现，否则会查询不正确

 

----“''”转义

SQL> select * from shall where test like '%''%';

TEST

--------------------------------------------------------------------------------

zhong 'zz

 

----转义字符为“]”

SQL> select * from shall where test like 'zhong ]_%' escape ']';

TEST

--------------------------------------------------------------------------------

zhong _zz

 

----转义字符为“\”

SQL> select * from shall where test like 'zhong \_%' escape '\';

TEST

--------------------------------------------------------------------------------

zhong _zz

 

----转义字符为“0”

SQL> select * from shall where test like 'zhong 0_%' escape '0';

TEST

--------------------------------------------------------------------------------

zhong _zz

 

----转义字符为“ ” （因为查询字段中包含一个空格，所以再用空格做转义字符明显不对）

SQL> select * from shall where test like 'zhong  _%' escape ' ';

TEST

--------------------------------------------------------------------------------

zhong _zz

zhong 'zz

zhong %zz

zhong %%%zz

zhong "&"zz

 

----转义字符为“\”

SQL> select * from shall where test like '%\%%' escape '\';

TEST

--------------------------------------------------------------------------------

zhong %zz

zhong %%%zz

 

 

SQL> select * from zz where zz like 'zhong\_\_%' escape '\';

ZZ

--------------------------------------------------------------------------------

zhong__

zhong__2

 

SQL> select * from zz where zz like 'zhong\_%\_%' escape '\';

ZZ

--------------------------------------------------------------------------------

zhong__

zhong__2

zhong_ _2

zhong_dd_2

zhong_dd__2

 

4.特殊字符“&”查询处理

SQL> select * from shall where test like '%\&%' escape '\';

select * from shall where test like '%\&%' escape '\'

                                    *

ERROR at line 1:

ORA-01424: missing or illegal character following the escape character

 

SQL> select ascii('&') from dual;

ASCII('&')

----------

        38

 

SQL> select chr(38) from dual;

C

-

&

 

SQL> select to_char('&') from dual;

T

-

&

 

SQL> select * from shall where test like '%'||chr(38)||'%';

TEST

--------------------------------------------------------------------------------

zhong "&"zz

shall &zhong

shall &zhong2

 

SQL> select * from shall where test like '%'||to_char('&')||'%';

TEST

--------------------------------------------------------------------------------

zhong "&"zz

shall &zhong

shall &zhong2

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2122303/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2122303/