文章內(nèi)容

Oracle escape函數(shù)的使用

發(fā)布時(shí)間: 2012/8/14 18:02:35

SQL> select * from t_char;A
--------------------
a_b
acb
a%b
a'b
a/b
a\b
%
_
aSQL> select * from t_char where a like 'a_b';A
--------------------
a_b
acb
a%b-

a'b
a/b
a\bSQL> 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--但是字符'還是需要用兩個(gè)''來代替字符串中的單引號(hào) 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ā)生邏輯錯(cuò)誤 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/\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 characterSQL> select * from t_char where a like 'a\\b' escape '\';A
--------------------
a\b
--要搜索"/"或者"\"則必須使用與之相同的符號(hào)作為轉(zhuǎn)義字符，否則無效     基本上的使用方法就是這些了，還需要注意一點(diǎn)，這里的escape函數(shù)，和sqlplus中的escape環(huán)境參數(shù)毫無關(guān)系
SQL> show escape
escape OFF