转载

SQL语句中特殊字符“&”如何转义

一、问题描述

在SQL语句中“&”符是特殊字符，代表环境变量的输入，比如（select '123&a' from dual; ）此处的&a就好比请给变量a赋值，如果我们就是想输出字符串&a这个字符呢，下面的实验中将会给出答案。其实大家会说用转义符就好了，但是这个字符有些特殊，用不了转义，不信可以自己试试。

二、实验

1.创建测试表及数据，以下字符都可以直接当字符串插入，这里的规则"abc特殊字符串def"

SAM@OCM11G >create table test(t varchar2(20));
Table created.
SAM@OCM11G >insert into test values ('abc!def');
1 row created.
SAM@OCM11G >insert into test values ('abc@def');
1 row created.
SAM@OCM11G >insert into test values ('abc#def');
1 row created.
SAM@OCM11G >insert into test values ('abc$def');
1 row created.
SAM@OCM11G >insert into test values ('abc%def');
1 row created.
SAM@OCM11G >insert into test values ('abc^def');
1 row created.
SAM@OCM11G >insert into test values ('abc*def');
1 row created.
SAM@OCM11G >insert into test values ('abc(def');
1 row created.
SAM@OCM11G >insert into test values ('abc)def');
1 row created.
SAM@OCM11G >insert into test values ('abc_def');
1 row created.
SAM@OCM11G >insert into test values ('abc+def');
1 row created.
SAM@OCM11G >insert into test values ('abc=def');
1 row created.
SAM@OCM11G >insert into test values ('abc-def');
1 row created.
SAM@OCM11G >insert into test values ('abc`def');
1 row created.
SAM@OCM11G >insert into test values ('abc~def');
1 row created.
SAM@OCM11G >insert into test values ('abc|def');
1 row created.
SAM@OCM11G >insert into test values ('abc;def');
1 row created.
SAM@OCM11G >insert into test values ('abc?/def');
1 row created.
SAM@OCM11G >insert into test values ('abc<>def');
1 row created.
SAM@OCM11G >commit;
Commit complete.

2.让我来测试一下’&’字符，直接使用&字符，无法得到想要的结果“abc&def”,在此处’&’字符做为输入变量参数的关键字来用

SAM@OCM11G >insert into test values ('abc&def'); --此处“&”当作输入符来对待
Enter value for def: &def --为了实现源字符，输入“&def”
old 1: insert into test values ('abc&def')
new 1: insert into test values ('abc&def')
1 row created.
SAM@OCM11G >select * from test;
T
--------------------
abc!def
abc@def
abc#def
abc$def
abc%def
abc^def
abc*def
abc(def
abc)def
abc_def
abc+def
abc=def
abc-def
abc`def
abc~def
abc|def
abc;def
abc?/def
abc<>def
abc&def
20 rows selected.

3.使用正常转义符，仍然报错：缺失或非法字符转义字符

（1）首先让我们使用转义符来查询一个带有’%’符号的数据

SCOTT@OCM11G >select * from test where t like '%/%%' escape '/';
T
--------------------
abc%def

（2）再让我们用该方法查询带有’&’符号的数据，结果失败

SCOTT@OCM11G >select * from test where t like '%/&%' escape '/';
select * from test where t like '%/&%' escape '/'
*
ERROR at line 1:
ORA-01424: missing or illegal character following the escape character
ORA-01424: 缺失或非法字符转义字符

4.通过&字符的ascii值来解决该问题

（1）查询出’&’字符的ascii值

SYS@OCM11G >select ascii('&') from dual;
ASCII('&')
----------
38

（2）将ascii值代入字符串中（注意使用连接符）

SCOTT@OCM11G >select 'abc'||chr(38)||'def' from dual;
'ABC'||
-------
abc&def
SCOTT@OCM11G >insert into test values ('abc'||chr(38)||'def');
1 row created.
SCOTT@OCM11G >commit;
Commit complete.

（3）验证最后插入数据

SCOTT@OCM11G >select * from test;
T
--------------------
abc!def
abc@def
… ...
abc<>def
abc&def --使用&&def插入
abc&def --使用ascii编码插入
21 rows selected.

三、总结

如果想使用特殊’&’字符当字符串来用时，可以使用ascii值来代替，这样可以解决SQL语句中的特殊需求。当然，这种变态的要求很难碰到。ORACLE小技巧，有时需要变通的思想，当然我也是借鉴了别的思路。学习不止，Sam继续加油！ Where there is a will, there is a way.

正文到此结束