Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks
Oracle的转义字符
 
    对于Oracle的转义字符,对然了解,但是一直不是很明白。而且在SQLPlus下的ESCAPE还和函数ESCAPE不是一个意思,更加容易搞混,所以专门来学习一下。首先可以通过几个简单的例子来了解一下这个函数在SQL中的用法。
 
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环境参数·毫·无·关·系·
 
SQL> show escape
escape OFF

 
posted on 2009-03-27 20:51 decode360 阅读(1862) 评论(0)  编辑  收藏 所属分类: 05.SQL

只有注册用户登录后才能发表评论。


网站导航: