Oracle escape函数的使用

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 OFFOracle中操作分页Oracle安装：“无法使用命令/usr/bin/xdpyinfo自动检相关资讯 oracle

[INS-32052] Oracle基目录和Oracle （07/22/2014 07:41:41）
Oracle 4个大对象（lobs）数据类型（02/03/2013 12:33:05）
Oracle按时间段分组统计（07/26/2012 10:36:48）

[Oracle] dbms_metadata.get_ddl的（07/12/2013 07:37:30）
Liferay Portal 配置使用Oracle和（07/31/2012 20:07:18）
Concurrent Request:Inactive （07/20/2012 07:44:05）

本文评论查看全部评论（0）