Oracle תÒå×Ö·û

Ò»¡¢×¼±¸ÌØÊâÊý¾Ý
create table t_escape(s varchar2(50));
--show define -- define "&" (hex 26)
--show escape -- escape off
set define off
set escape on
insert into t_escape values('string&text');
insert into t_escape values('string\&text');
insert into t_escape values('string\%text');
insert into t_escape values('string\_text');
insert into t_escape values('string''text');
set escape off
set define on
insert into t_escape values('Top_Gun');
insert into t_escape values('Tom''s eye');
insert into t_escape values('normal');
insert into t_escape values('100%ps');
insert into t_escape values('12345');
insert into t_escape values('15%30');
insert into t_escape values('%40');
insert into t_escape values('25%XT');
insert into t_escape values('%%A3E5');
set define off
--Ö±½Ó²åÈëÈçÏÂÊý¾Ýʱ£¬²åÈë½á¹û²»ÕýÈ·¡£
insert into t_escape values('T&T Company');
set define on
commit;
/
¶þ¡¢Ê¹ÓÃתÒå²éѯ
[½ç¶¨·û][תÒå·û]%'escape'[½ç¶¨·û] ΪתÒ嶨Ò壬Èç
תÒå_£¬Ê¹ÓÃ$_%'escape'$£¬ÕâÀï½ç¶¨·ûÊÇ$¡£
select * from t_escape where s like 'Top$_%'escape'$';
½ç¶¨·û¿ÉÒÔÊÇ¿Õ¸ñ,\,=,/,|£¬²»ÄÜÊÇ×Öĸ¡£
Àý£¬²éÕÒº¬ÓÐ'%'µÄËùÓÐ×ֶΣº
select * from t_escape where s like '%\%%' escape '\';
'&'²»ÄÜͨ¹ýתÒå×Ö·û²éÕÒ£º
select * from t_escape where s like '%\&%' escape'\';
¿ÉÒÔͨ¹ýÁíÍâµÄ·½Ê½½øÐÐתÒ壺
select ascii('&') from dual; -- ASCII = 38
select * from t_escape where s like '%'||chr(38)||'%';
²éÕÒµ¥ÒýºÅ£º
select * from t_escape where s like '%''%';