escaping special characters in Oracle - 转义Oracle的特殊字符

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/8/12
Subject: oracle特殊字符的转义 escaping special characters in oracle
To: xiewenxiewen at gmail.com


1.sqlplus中的&符号转义
sqlplus中, 默认的，&符号有特殊含义，&符号后面加上一个字符串名字表示引用用户定义的一个字符串变量，比如:
_USER是sqlplus默认定义的一个字符串变量，用&_USER表示引用这个变量.
变量a没有定义，会提示用户输入


如果想把&当成普通字符处理，比如就想查询出'&a'这个字符串，有几种方法
a. set define
修改默认的变量引用符号
show define
set define '$'
show define
select '&a' from dual;

变量引用符号改成了$，直接查询'&a'，结果就是'&a'

b. set define off
关闭引用变量功能
set define off

也可以直接查询出'&a'

c. set escape设置转义字符
set define '&'
show define
show escape
set escape '\'
show escape
select '\&a','\\'
from dual;

&前面加上了\转义字符，表示&不再带有特殊含义，而当成普通字符处理


d. 用||连接符号把&和后面的字符分开
select '&'||'a'
from dual;

单独一个&不具有特殊含义

或
select chr(38)||'a' from dual;

38是&符号的ascii编码


2.转义单引号''

a. 单引号本身就是转义符
select 'a''bc'
from dual;

ab之间连着写的两个单引号就代表一个单引号

单引号转义多用于pl/sql执行动态语句,如:
drop table t;
create table t(a varchar2(10), b varchar2(10));
declare
v_tab varchar2(30) := 't';
begin
execute immediate 'insert into '|| v_tab ||' values (''a'',''''''b'')';
end;
/
select * from t;


b. 10g以前，字符串只能放在两个单引号之间。10g新增了语法，q'?......?'，可以定义自己的引号符代替单引号
比如，用双引号或其他符号代替单引号
select q'"a'bc"' from dual;


select q'!a'bc!' from dual;
select q'xa'bcx' from dual;
select q'1a'bc1' from dual;
select q''a'bc'' from dual;
select q'谢a'bc谢' from dual;


用成对的括号也可以
select q'{a'bc}' from dual;
select q'[a'bc]' from dual;
select q'<a'bc>' from dual;
select q'(a'bc)' from dual;


c. chr函数和连接字符串
select ascii('''') from dual;
select 'a'||chr(39)||'bc' from dual;

39是单引号的ascii编码


3. 转义like查询中的通配符_和%
like查询有两种通配符, _下划线代表任意一个字符，%百分号代表任意个字符

比如，想查询含有_io_的参数
select name from v$parameter where name like '%_io_%' order by 1;

因为要查询的字段有通配符，'%_io_%' 表示含有?io?的字段, 所以operation,session什么的参数也查出来了，没有得到预想的结果

a. 加上escape转义符
select name from v$parameter where name like '%\_io\_%' escape '\' order by 1;

自定义\反斜杠为转义符，并对_下划线转义
查询结果正确

b.用instr
select name from v$parameter where instr(name,'_io_')>0 order by 1;


c.用translate或replace
用translate或replace函数将_下划线替换成别的字符，然后再比较
select name from v$parameter where translate(name,'_','#') like '%#io#%' order by 1;


或
select name from v$parameter where replace(name,'_','#') like '%#io#%' order by 1;



d.用正则表达式regular expression
10g下用regexp_like或regexp_instr:
select name from v$parameter where regexp_like(name,'.*_io_.*') order by 1;


或
select name from v$parameter where regexp_instr(name,'.*_io_.*')>0 order by 1;

'.*_io_.*'是正则表达式的模式pattern

9i sql语句中不支持正则表达式，没有regexp_like，得调用owa_pattern包
create or replace function my_match (p_str in varchar2, p_pat in varchar2)
return number
is
begin
if (owa_pattern.match(p_str, p_pat)) then
return 1;
else
return 0;
end if;
end;
/
show err
select name from v$parameter where my_match(name,'.*_io_.*')>0 order by 1;

速度很慢很慢


Xie wen（谢文）
Network & Operations, Mobile Software Solutions (MDB) MOTOROLA Inc.
No. 108 Jian Guo Road, Chao Yang District, Beijing 100022 P. R. China
e-mail wenxie at motorola.com



-fin-