一位同事在使用SQL处理一串字符时，出现一个意料之外的问题：这个字符串中包括字符‘&’。我们先看一下现象：
| SQL> select * from v$version; BANNER—————————————————————-Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – ProdPL/SQL Release 10.2.0.1.0 – ProductionCORE 10.2.0.1.0 ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 – ProductionNLSRTL 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 escapeescape OFF– 打开escape，默认使用’\’作为转义字符SQL> set escape onSQL> show escapeescape “\” (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 escapeescape “#” (hex 23)SQL> select substr(‘asdf\\\#&yuechaotian’, 5) from dual; SUBSTR(‘ASDF\\\—————\\\&yuechaotian |
http://blog.chinaunix.net/uid-298599-id-2443124.html
