oracle的转义字符

  xdoclet在hibernate中的用法总结  linux 删除指定日期之前的文件
oracle的转义字符   2009-08-03 13:35:51|  分类： SQL |  标签： |字号大
中
小 订阅 


    Oracle的转义字符需要用ESCAPE函数来定义，小小学习一下： 
 
 
SQL> create table t_char(a varchar2(20)); 
SQL> insert into t_char values('a_b'); 
SQL> insert into t_char values('acb'); 
SQL> insert into t_char values('a%b'); 
SQL> insert into t_char values('a''b'); 
SQL> insert into t_char values('a/b'); 
SQL> insert into t_char values('a\b'); 
SQL> insert into t_char values('%'); 
SQL> insert into t_char values('_'); 
SQL> insert into t_char values('a'); 
SQL> commit; 
 
SQL> select * from t_char; 
A
--------------------
a_b
acb
a%b
a'b
a/b
a\b
%
_
a 
 
SQL> select * from t_char where a like 'a_b'; 
A
--------------------
a_b
acb
a%b
a'b
a/b
a\b 
 
SQL> 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/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 '\'; 
A
--------------------
a\b

--要搜索"/"或者"\"则必须使用与之相同的符号作为转义字符，否则无效 
 
 
 
 
    基本上的使用方法就是这些了，还需要注意一点，这里的escape函数，和sqlplus中的escape环境参数·毫·无·关·系·