oracle 转义字符 换行符,Oracle的转义字符

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环境参数·毫·无·关·系·

SQL> show escape

escape OFF

-The End-