Oracle SQL 转义字符(用于 a’&’)

在尝试使用 Oracle SQL 开发人员执行 SQL 插入语句时，我不断生成一个“输入替换值”提示符:

insert into agregadores_agregadores
(
idagregador,
nombre,
url
)
values
(
2,
'Netvibes',
'http://www.netvibes.com/subscribe.php?type=rss\&amp;url='
);

我已经使用上面的“”尝试了转义查询中的特殊字符，但仍然无法避免与号“ &”，从而导致字符串替换。

318139 次浏览

小开

insert into AGREGADORES_AGREGADORES (IDAGREGADOR,NOMBRE,URL)
values (2,'Netvibes',
'http://www.netvibes.com/subscribe.php?type=rss' || chr(38) || 'amp;url=');

小开

Set the define character to something other than &

SET DEFINE ~
create table blah (x varchar(20));
insert into blah (x) values ('blah&amp');
select * from blah;


X
--------------------
blah&amp

小开

最佳答案

the & is the default value for DEFINE, which allows you to use substitution variables. I like to turn it off using

SET DEFINE OFF

then you won't have to worry about escaping or CHR(38).

小开

|| chr(38) ||

This solution is perfect.

小开

select 'one'||'&'||'two' from dual

小开

SELECT 'Free &' || ' Clear' FROM DUAL;

小开

The real answer is you need to set the escape character to '\': SET ESCAPE ON

The problem may have occurred either because escaping was disabled, or the escape character was set to something other than '\'. The above statement will enable escaping and set it to '\'.

None of the other answers previously posted actually answer the original question. They all work around the problem but don't resolve it.

小开

add this before your request

set define off;