共计 2812 个字符，预计需要花费 8 分钟才能阅读完成。
一、问题描述
在 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.
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-01/139040.htm
正文完
星哥玩云-微信公众号
