blocks|key|236251|text|您可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|236252|set+define+off|code-block|syntax|javascript|236253|使用此选项将不会提示输入|236254|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can use

<pre><code>set define off
</code></pre>

Using this it won't prompt for the input

blocks|key|236288|text|Escape+is+set+to+\+by+default，所以您不需要设置它；但是如果您设置了它，请不要用引号括起来。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|236289|“&”是SQL*Plus的substitution+variable标记；但是您可以使用change+it，或者在本例中更有用的是完全关闭它，方法是：|236290|set+define+off|code-block|syntax|javascript|236291|然后，您根本不需要费心转义该值。|236292|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#BGBFAHDB|1|http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_five.htm#sthref453|2|http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#i2698854^0|H|1|0|T|0|0|D|L|1|18|9|2|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@$9|13|A|14|1|15]]|E|$]]|$1|F|3|G|5|6|7|16|8|@]|D|@$9|17|A|18|1|19]|$9|1A|A|1B|1|1C]]|E|$]]|$1|H|3|I|5|J|7|1D|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1E|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]|W|$5|R|S|T|E|$U|X]]|Y|$5|R|S|T|E|$U|Z]]]]

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#BGBFAHDB" rel="noreferrer">Escape is set to <code>\</code> by default</a>, so you don't need to set it; but if you do, don't wrap it in quotes.

Ampersand is the SQL*Plus <a href="http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_five.htm#sthref453" rel="noreferrer">substitution variable</a> marker; but you can <a href="http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#i2698854" rel="noreferrer">change it</a>, or more usefully in your case turn it off completely, with:

<pre><code>set define off
</code></pre>

Then you don't need to bother escaping the value at all.

blocks|key|236454|text|而不是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|236455|node_name+=+'Geometric+Vectors+\&+Matrices'|code-block|syntax|javascript|236456|使用|236457|node_name+=+'Geometric+Vectors+'+%7C%7C+chr(38)+%7C%7C+'+Matrices'+|236458|38是“与”符号的ascii代码，在这种形式下，它将被解释为字符串，而不是其他。我试过了，它起作用了。|236459|另一种方法是使用LIKE和下划线代替'&‘字符：|236460|node_name+LIKE+'Geometric+Vectors+_+Matrices'+|236461|你找到其他记录的机会也很低，只有一个字符有所不同。|236462|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Instead of 

<pre><code>node_name = 'Geometric Vectors \&amp; Matrices'
</code></pre>

use 

<pre><code>node_name = 'Geometric Vectors ' || chr(38) || ' Matrices' 
</code></pre>

38 is the ascii code for ampersand, and in this form it will be interpreted as a string, nothing else. I tried it and it worked. 

Another way could be using LIKE and an underline instead the '&amp;' character: 

<pre><code>node_name LIKE 'Geometric Vectors _ Matrices' 
</code></pre>

The chance that you'll find some other record too, which is different in only this one character, is quite low.

blocks|key|236482|text|直接来自oracle+sql基础知识一书。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|236483|SET+DEFINE+OFF
select+'Coda+&+Sid'+from+dual;
SET+DEFINE+ON|code-block|syntax|javascript|236484|如何在不设置定义的情况下转义它。|236485|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

straight from oracle sql fundamentals book 

<pre><code>SET DEFINE OFF
select 'Coda &amp; Sid' from dual;
SET DEFINE ON
</code></pre>

how would one escape it without setting define.

blocks|key|454730|text|REPLACE(<your+xml+column>,'&',chr(38)%7C%7C'amp;')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|454731|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>REPLACE(&lt;your xml column&gt;,'&amp;',chr(38)||'amp;')
</code></pre>

blocks|key|236528|text|这也是可行的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|236529|select+*+from+mde_product+where+cfn+=+'A3D"&"R01'|offset|length|style|CODE|236530|通过在字符串中包含is和双qoutes+"&"，可以将&定义为文字。|236531|entityMap^0|0|0|1D|0|K|3|R|1|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|K|$]]

This works as well:

<code>select * from mde_product where cfn = 'A3D"&amp;"R01'</code>

you define <code>&amp;</code> as literal by enclosing is with double qoutes <code>"&amp;"</code> in the string.

blocks|key|454822|text|set+escape+on
...+node_name+=+'Geometric+Vectors+\&+Matrices'+...|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|454823|或者：|unstyled|454824|set+define+off
...+node_name+=+'Geometric+Vectors+&+Matrices'+...|454825|第一个允许您使用反斜杠来转义&。|454826|第二个关闭&+"globally“(不需要在任何地方添加反斜杠)。您可以通过set+define+on再次打开它，从与号上的那一行将重新获得它们的特殊含义，因此您可以在脚本的某些部分关闭它，而不是在其他部分。|offset|length|style|CODE|454827|entityMap^0|0|0|0|0|12|D|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|T|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@$M|X|N|Y|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|F|7|Z|8|@]|9|@]|A|$]]]|R|$]]

<pre><code>set escape on
... node_name = 'Geometric Vectors \&amp; Matrices' ...
</code></pre>

or alternatively:

<pre><code>set define off
... node_name = 'Geometric Vectors &amp; Matrices' ...
</code></pre>

The first allows you to use the backslash to escape the &amp;.

The second turns off &amp; "globally" (no need to add a backslash anywhere). You can turn it on again by <code>set define on</code> and from that line on the ampersands will get their special meaning back, so you can turn it off for some parts of the script and not for others.

blocks|key|236869|text|我写了一个正则表达式来帮助查找和替换插入中的"&“，我希望这对某些人有帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|236870|诀窍是确保"&“与其他文本在一起。|236871|查找“(\'[%5E\']*(?=\&))(\&)([%5E\']*\')”|offset|length|style|CODE|236872|替换“$1'+%7C%7C+chr(38)+%7C%7C+'$3”|236873|entityMap^0|0|0|2|W|0|2|N|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@$F|Q|G|R|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@$F|T|G|U|H|I]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|M|$]]

I wrote a regex to help find and replace "&amp;" within an INSERT, I hope that this helps someone.

The trick was to make sure that the "&amp;" was with other text.

Find <code>“(\'[^\']*(?=\&amp;))(\&amp;)([^\']*\')”</code>

Replace <code>“$1' || chr(38) || '$3”</code>

blocks|key|454925|text|我知道这很糟糕。上述方法都没有真正起作用，但我找到了一个变通的方法。请特别注意在撇号‘之间使用空格，否则它将被转义。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|454926|SELECT+'Hello+'+'&'+'+World';

Hello+&+World|code-block|syntax|javascript|454927|不客气;)|454928|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I know it sucks. None of the above things were really working, but I found a work around. Please be extra careful to use spaces between the apostrophes [ ' ], otherwise it will get escaped.
<pre><code>SELECT 'Hello ' '&amp;' ' World';

Hello &amp; World
</code></pre>
You're welcome ;)

blocks|key|454934|text|--SUBSTITUTION+VARIABLES
--+these+variables+are+used+to+store+values+TEMPorarily.
--+The+values+can+be+stored+temporarily+through
--+Single+Ampersand+(&)
--+Double+Ampersand(&&)
--+The+single+ampersand+substitution+variable+applies+for+each+instance+when+the
--SQL+statement+is+created+or+executed.
--+The+double+ampersand+substitution+variable+is+applied+for+all+instances+until
--that+SQL+statement+is+existing.
INSERT+INTO+Student+(Stud_id,+First_Name,+Last_Name,+Dob,+Fees,+Gender)
VALUES+(&stud_Id,+'&First_Name'+,'&Last_Name',+'&Dob',+&fees,+'&Gender');
--Using+double+ampersand+substitution+variable
INSERT+INTO+Student+(Stud_id,First_Name,+Last_Name,Dob,Fees,Gender)
VALUES+(&stud_Id,+'&First_Name'+,'&Last_Name',+'&Dob',+&&fees,'&gender');|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|454935|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>--SUBSTITUTION VARIABLES
-- these variables are used to store values TEMPorarily.
-- The values can be stored temporarily through
-- Single Ampersand (&amp;)
-- Double Ampersand(&amp;&amp;)
-- The single ampersand substitution variable applies for each instance when the
--SQL statement is created or executed.
-- The double ampersand substitution variable is applied for all instances until
--that SQL statement is existing.
INSERT INTO Student (Stud_id, First_Name, Last_Name, Dob, Fees, Gender)
VALUES (&amp;stud_Id, '&amp;First_Name' ,'&amp;Last_Name', '&amp;Dob', &amp;fees, '&amp;Gender');
--Using double ampersand substitution variable
INSERT INTO Student (Stud_id,First_Name, Last_Name,Dob,Fees,Gender)
VALUES (&amp;stud_Id, '&amp;First_Name' ,'&amp;Last_Name', '&amp;Dob', &amp;&amp;fees,'&amp;gender');
</code></pre>

I am trying to query a certain row by name in my sql database and it has an ampersand. I tried to set an escape character and then escape the ampersand, but for some reason this isn't working and I'm uncertain as to what exactly my problem is.

<pre><code>Set escape '\'
 select * from V1144engine.T_nodes where node_id in(
 select node2_id from V1144engine.T_edges where node1_id in(
 select node2_id from V1144engine.T_edges where node1_id in(
 select node2_id from V1144engine.T_edges where node1_id = 
 (select node_id from V1144engine.T_nodes where node_name = 'Geometric Vectors \&amp; Matrices')))
 and edge_type_id = 1)
 and node_type_id = 1
 and node_id in (
 select node2_id from V1144engine.T_edges where node1_id =
 (select node_id from V1144engine.T_nodes where node_name = 'Algebra II')
 and edge_type_id = 2);
</code></pre>

Although this has a similar solution to <a href="https://stackoverflow.com/questions/118190/how-do-i-ignore-ampersands-in-a-sql-script-running-from-sql-plus">this question</a>, the problems are posed very differently. They may end up having the same solution, but that does not mean that the questions are the same.

Escaping ampersand character in SQL string

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在尝试按名称查询sql数据库中的某一行，但它有一个“与”号。我试图设置一个转义字符，然后转义与号，但由于某种原因，这不起作用，我不确定我的问题到底是什么。Set escape '\'    select * from V1144engine.T_nodes where node_id in(    select n...

问转义SQL字符串中的“与”字符
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问转义SQL字符串中的“与”字符EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问转义SQL字符串中的“与”字符
EN