Oracle escape函数的使用

ERROR at line 1:ORA-01424: missing or illegal character following the escape characterSQLgt; select * from t_char where

sql> select * from t_char;a
--------------------
a_b
acb
a%b
a'b
a/b
a
%
_
asql> select * from t_char where a like 'a_b';a
--------------------
a_b
acb
a%b
a'b
a/b
asql> select * from t_char where a like 'a_b' escape '';a
--------------------
a_b--可见要查询含有通配字符的字符串，必须要使用escape函数 sql> select * from t_char where a like 'a'b' escape '';
error:
ora-01756: quoted string not properly terminated
sql> select * from t_char where a like 'a''b' escape ''; a
--------------------
a'b--但是字符'还是需要用两个''来代替字符串中的单引号 sql> select * from t_char where a like 'ax_b' escape 'x'; a
--------------------
a_b sql> select * from t_char where a like 'a''_b' escape ''''; a
--------------------
a_b sql> select * from t_char where a like 'a_%b' escape '_'; a
--------------------
a%b --可以用其他字符，甚至是“'”、“_”、“%”来用作转义字符，但是一般都使用，以免发生逻辑错误 sql> select * from t_char where a like 'a/b' escape '';
select * from t_char where a like 'a/b' escape ''
                                  *
error at line 1:
ora-01424: missing or illegal character following the escape character
sql> select * from t_char where a like 'a_/b' escape '_';
select * from t_char where a like 'a_/b' escape '_'
                                  *
error at line 1:
ora-01424: missing or illegal character following the escape character sql> select * from t_char where a like 'a//b' escape '/';a
--------------------
a/bsql> select * from t_char where a like 'a/' escape '/';
select * from t_char where a like 'a/' escape '/'
                                  *
error at line 1:
ora-01424: missing or illegal character following the escape charactersql> select * from t_char where a like 'a\b' escape '';a
--------------------
a
--要搜索"/"或者""则必须使用与之相同的符号作为转义字符，，否则无效     基本上的使用方法就是这些了，还需要注意一点，这里的escape函数，和sqlplus中的escape环境参数毫无关系
sql> show escape
escape off