oracle的一些字符转义

7 rows selected.

SQL> select * from tmp_xf where text like 'abc\_%' escape '\';

TEXT
--------------------
abc_defg

转义字符为'\';

SQL> select * from tmp_xf where text like 'abc _%' escape ' ';

TEXT
--------------------
abc_defg

转义字符为' ';

'&'不能通过转义字符查找：

SQL> select * from tmp_xf where text like '%\&%' escape'\';
select * from tmp_xf where text 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 * from tmp_xf where text like '%'||chr(38)||'%';

TEXT
--------------------
abc&defg

单引号的转义，使用两个连续的单引号：

SQL> insert into tmp_xf values ('abc''defg');

1 row created.

SQL> select * from tmp_xf where text like '%''%';

TEXT
--------------------
abc'defg

特殊符号的数据的插入

SQL> insert into tmp_xf values ('abc'||chr(38)||'xxxx');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tmp_xf where text like '%xx';

TEXT
--------------------
abc&xxxx

其实简单化的方法是设置参数
set define off;
这样可以方便的inset特殊字符