字符串中的转义字符

一位同事在使用SQL处理一串字符时，出现一个意料之外的问题：这个字符串中包括字符‘&’。我们先看一下现象：

 

SQL> select * from v$version;   BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE    10.2.0.1.0      Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production   SQL> select substr('asdf&yuechaotian', 1, 1) from dual; 输入 yuechaotian 的值: 原值    1: select substr('asdf&yuechaotian', 1, 1) from dual 新值    1: select substr('asdf', 1, 1) from dual   S - a

 

因为这里的&提示后面的字符串是一个变量，提示我输入子串yuechaotian的值。直接输入回车后，这个字符串就变成'asdf'，进行substr操作。比如此时我取第5个字符，就会返回null了：

 

SQL> select substr('asdf&yuechaotian', 5, 1) from dual; 输入 yuechaotian 的值: 原值    1: select substr('asdf&yuechaotian', 5, 1) from dual 新值    1: select substr('asdf', 5, 1) from dual   S -

 

此时只要将字符‘&’转义一下就好。转义字符是由SQL*PLUS的参数escape来控制的：

 

-- 默认情况下，escape是关闭的 SQL> show escape escape OFF -- 打开escape，默认使用'/'作为转义字符 SQL> set escape on SQL> show escape escape "/" (hex 5c) -- 此时使用'/'将字符'&'转义 SQL> select substr('asdf/&yuechaotian', 1, 1) from dual;   S - a   -- 转义字符只是用来转义，本身是不占字符位置的 SQL> select substr('asdf/&yuechaotian', 5, 1) from dual;   S - &

 

转义字符只转义它后面的一个字符：

 

-- 此时第一个'/'将第二个'/'转义，&表示后面的子串yuechaotian是变量，不输入任何值，得到子串'asdf/' SQL> select substr('asdf//&yuechaotian', 5, 1) from dual; 输入 yuechaotian 的值: 原值    1: select substr('asdf//&yuechaotian', 5, 1) from dual 新值    1: select substr('asdf/', 5, 1) from dual   S - /   -- 此时第一个'/'将第二个'/'转义，第三个'/'将'&'转义，得到子串'asdf/&yuechaotian' SQL> select substr('asdf///&yuechaotian', 5, 1) from dual;   S - /   SQL> select substr('asdf///&yuechaotian', 5) from dual;   SUBSTR('ASDF/ ------------- /&yuechaotian

 

其实你也可以自己制定转义字符，不一定要使用'/'。比如你的字符串里本身就有字符'/'，你需要找一个特殊点的字符作为转义字符：

 

SQL> set escape # SQL> show escape escape "#" (hex 23) SQL> select substr('asdf///#&yuechaotian', 5) from dual;   SUBSTR('ASDF/// --------------- ///&yuechaotian