SQL 如何插入包含“&”的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/152837/
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
How to insert a string which contains an "&"
提问by Andrew Hampton
How can I write an insert statement which includes the & character? For example, if I wanted to insert "J&J Construction" into a column in the database.
如何编写包含 & 字符的插入语句？例如，如果我想将“J&J Construction”插入到数据库中的列中。
I'm not sure if it makes a difference, but I'm using Oracle 9i.
我不确定它是否有所作为，但我使用的是 Oracle 9i。
回答by tardate
I keep on forgetting this and coming back to it again! I think the best answer is a combination of the responses provided so far.
我不断地忘记这一点并再次回到它！我认为最好的答案是迄今为止提供的答复的组合。
Firstly, & is the variable prefix in sqlplus/sqldeveloper, hence the problem - when it appears, it is expected to be part of a variable name.
首先，& 是 sqlplus/sqldeveloper 中的变量前缀，因此存在问题 - 当它出现时，它应该是变量名的一部分。
SET DEFINE OFF will stop sqlplus interpreting & this way.
SET DEFINE OFF 将停止 sqlplus 解释 & 这样。
But what if you need to use sqlplus variables andliteral & characters?
但是如果您需要使用 sqlplus 变量和文字 & 字符怎么办？
- You need SET DEFINE ON to make variables work
- And SET ESCAPE ON to escape uses of &.
- 您需要 SET DEFINE ON 才能使变量起作用
- 和 SET ESCAPE ON 来逃避 & 的使用。
e.g.
例如
set define on
set escape on
define myvar=/forth
select 'back\ \& &myvar' as swing from dual;
Produces:
产生：
old 1: select 'back\ \& &myvar' from dual
new 1: select 'back\ & /forth' from dual
SWING
--------------
back\ & /forth
If you want to use a different escape character:
如果要使用不同的转义字符：
set define on
set escape '#'
define myvar=/forth
select 'back\ #& &myvar' as swing from dual;
When you set a specific escape character, you may see 'SP2-0272: escape character cannot be alphanumeric or whitespace'. This probably means you already have the escape character defined, and things get horribly self-referential. The clean way of avoiding this problem is to set escape off first:
当您设置特定的转义字符时，您可能会看到“SP2-0272：转义字符不能是字母数字或空格”。这可能意味着您已经定义了转义字符，并且事情变得非常自我参照。避免这个问题的干净方法是首先设置转义：
set escape off
set escape '#'
回答by hamishmcn
If you are doing it from SQLPLUS use
如果您是从 SQLPLUS 执行此操作，请使用
SET DEFINE OFF
to stop it treading & as a special case
阻止它踩踏并作为特例
回答by C. J.
An alternate solution, use concatenation and the chr function:
另一种解决方案，使用串联和 chr 函数：
select 'J' || chr(38) || 'J Construction' from dual;
回答by Hans
There's always the chr() function, which converts an ascii code to string.
总是有 chr() 函数，它将 ascii 代码转换为字符串。
ie. something like: INSERT INTO table VALUES ( CONCAT( 'J', CHR(38), 'J' ) )
IE。类似于：插入表值（CONCAT（'J'，CHR（38），'J'））
回答by Greg Ogle
The correct syntax is
正确的语法是
set def off;
insert into tablename values( 'J&J');
回答by hamishmcn
SET SCAN OFF is obsolete http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a90842/apc.htm
SET SCAN OFF 已过时 http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a90842/apc.htm
回答by Mike Dimmick
In a program, always use a parameterized query. It avoids SQL Injection attacks as well as any other characters that are special to the SQL parser.
在程序中，始终使用参数化查询。它避免了 SQL 注入攻击以及 SQL 解析器特有的任何其他字符。
回答by lokesh kumar
You can insert such an string as 'J'||'&'||'Construction'. It works fine.
您可以插入诸如'J'||'&'||'Construction' 之类的字符串。它工作正常。
insert into table_name (col_name) values('J'||'&'||'Construction');
回答by aemre
INSERT INTO TEST_TABLE VALUES('Jonhy''s Sport &'||' Fitness')
This query's output : Jonhy's Sport & Fitness
此查询的输出：Jonhy's Sport & Fitness
回答by Andrew Hampton
I've found that using either of the following options works:
我发现使用以下任一选项都有效：
SET DEF OFF
SET DEF OFF
or
或者
SET SCAN OFF
SET SCAN OFF
I don't know enough about databases to know if one is better or "more right" than the other. Also, if there's something better than either of these, please let me know.
我对数据库知之甚少，无法知道一个数据库是否比另一个更好或更“正确”。另外，如果有比这两者更好的东西，请告诉我。
