如何插入一个包含“&” [英] How to insert a string which contains an "&"

问题描述

如何编写一个insert语句，其中包含&字符？例如，如果我想在数据库中的一列中插入J& J Construction。

我不知道这是否有所作为，使用Oracle 9i。

解决方案

我继续忘记这个，再次回来！我认为最佳答案是迄今为止提供的答复的组合。

首先，&是sqlplus / sqldeveloper中的变量前缀，因此问题出现时，预计将成为变量名的一部分。

SET DEFINE OFF将停止sqlplus解释&安培;

但是，如果您需要使用sqlplus变量和文字和&字符？

• 您需要SET DEFINE ON才能使变量工作


• 并设置ESCAPE ON以避免使用&。

例如

  set define on 
 set $ on 
 
定义myvar = /之前
 
选择'back\\ \& & myvar'从双重摆动; 
  

产生：

 code> old 1：选择'back\\ \& & myvar'from dual 
 new 1：select'back\& / four'from dual 
 
 SWING 
 -------------- 
 back\& / $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 
 
  set $ on 
 set escape'＃'
 
定义myvar = /之前
 
选择'后面加上＃& & myvar'从双重摆动; 
  
当您设置特定的转义字符时，您可能会看到SP2-0272：转义字符不能为字母数字或空白。这可能意味着你已经定义了转义字符，事情变得非常自我参照。避免这个问题的干净方法是首先设置逃脱：
 设置逃脱
设置转义'＃' 
  
 
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.

I'm not sure if it makes a difference, but I'm using Oracle 9i.
解决方案
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.

SET DEFINE OFF will stop sqlplus interpreting & this way.

But what if you need to use sqlplus variables and literal & characters? 


You need SET DEFINE ON to make variables work
And SET ESCAPE ON to escape uses of &.


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:
set escape off
set escape '#'


                        
这篇关于如何插入一个包含“&”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！