Oracle SQL 转义字符（用于“&”）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1137354/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL escape character (for a '&')
提问by ian_scho
While attempting to execute SQL insert statements using Oracle SQL DeveloperI keep generating an "Enter substitution value" prompt:
在尝试使用Oracle SQL Developer执行 SQL 插入语句时，我不断生成“输入替换值”提示：
insert into agregadores_agregadores
(
idagregador,
nombre,
url
)
values
(
2,
'Netvibes',
'http://www.netvibes.com/subscribe.php?type=rss\&url='
);
I've tried escaping the special character in the queryusing the '\' above but I still can't avoid the ampersand, '&', causing a string substitution.
我已经尝试使用上面的 '\'转义查询中的特殊字符，但我仍然无法避免 & 符号，'&'，导致字符串替换。
回答by Neil Kodner
the & is the default value for DEFINE, which allows you to use substitution variables. I like to turn it off using
& 是 DEFINE 的默认值，它允许您使用替换变量。我喜欢关闭它使用
SET DEFINE OFF
设置定义关闭
then you won't have to worry about escaping or CHR(38).
那么你就不必担心转义或 CHR(38)。
回答by Aseem
|| chr(38) ||
|| chr(38) ||
This solution is perfect.
这个解决方案是完美的。
回答by RC.
Set the define character to something other than &
将定义字符设置为 & 以外的其他字符
SET DEFINE ~
create table blah (x varchar(20));
insert into blah (x) values ('blah&');
select * from blah;
X
--------------------
blah&
回答by Jeffrey Kemp
insert into AGREGADORES_AGREGADORES (IDAGREGADOR,NOMBRE,URL)
values (2,'Netvibes',
'http://www.netvibes.com/subscribe.php?type=rss' || chr(38) || 'amp;url=');
回答by drj
SELECT 'Free &' || ' Clear' FROM DUAL;
回答by Izo
select 'one'||'&'||'two' from dual
select 'one'||'&'||'two' from dual
回答by Terrible Tadpole
The real answer is you need to set the escape character to '\': SET ESCAPE ON
真正的答案是您需要将转义字符设置为“\”：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 andset 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.
之前发布的其他答案都没有真正回答原始问题。他们都解决了这个问题，但没有解决它。
回答by Helali
add this before your request
在您的请求之前添加此内容
set define off;
