Oracle的轉(zhuǎn)義字符
?
??? 對于Oracle的轉(zhuǎn)義字符，對然了解，但是一直不是很明白。而且在SQLPlus下的ESCAPE還和函數(shù)ESCAPE不是一個意思，更加容易搞混，所以專門來學(xué)習(xí)一下。首先可以通過幾個簡單的例子來了解一下這個函數(shù)在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函數(shù)
?
?
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
?
--可以用其他字符，甚至是“'”、“_”、“%”來用作轉(zhuǎn)義字符，但是一般都使用\，以免發(fā)生邏輯錯誤
?
?
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
?
--要搜索"/"或者"\"則必須使用與之相同的符號作為轉(zhuǎn)義字符，否則無效
?
?
--基本上的使用方法就是這些了，最后再強(qiáng)調(diào)一下：這里的escape函數(shù)，和sqlplus中的escape環(huán)境參數(shù)·毫·無·關(guān)·系·
?
SQL> show escape
escape OFF
?