How to insert a string which contains an

Question

How can I write an insert statement which includes the  amp  character   For example  if I wanted to insert  J amp J Construction  into a column in the database   I m not sure if it makes a difference  but I m using Oracle 9i

User · Answer

An alternate solution  use concatenation and the chr function   select  J     chr 38      J Construction  from dual

User · Answer

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   amp  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  amp  this way   But what if you need to use sqlplus variables and literal  amp  characters     You need SET DEFINE ON to make variables work And SET ESCAPE ON to escape uses of  amp     e g   set define on set escape on  define myvar  forth  select  back     amp   amp myvar  as swing from dual    Produces   old   1  select  back     amp   amp myvar  from dual new   1  select  back   amp   forth  from dual  SWING -------------- back   amp   forth   If you want to use a different escape character   set define on set escape      define myvar  forth  select  back    amp   amp 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

User · Answer

There s always the chr   function  which converts an ascii code to string   ie  something like  INSERT INTO  table VALUES    CONCAT   J   CHR 38    J

User · Answer

I ve found that using either of the following options works   SET DEF OFF   or  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

User · Answer

Stop using SQL Plus  I highly recommend PL SQL Developer  it s much more than an SQL tool   p s  Some people prefer TOAD

User · Answer

SET SCAN OFF is obsolete http   download-uk oracle com docs cd B10501 01 server 920 a90842 apc htm

User · Answer

If you are doing it from SQLPLUS use   SET DEFINE OFF     to stop it treading  amp  as a special case

User · Answer

The correct syntax is  set def off  insert into tablename values   J amp J

User · Answer

There s always the chr   function  which converts an ascii code to string   ie  something like  INSERT INTO  table VALUES    CONCAT   J   CHR 38    J

User · Answer

Look  Andrew    J amp J Construction    SELECT CONCAT  J   CONCAT CHR 38    J Construction    FROM DUAL

User · Answer

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   amp  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  amp  this way   But what if you need to use sqlplus variables and literal  amp  characters     You need SET DEFINE ON to make variables work And SET ESCAPE ON to escape uses of  amp     e g   set define on set escape on  define myvar  forth  select  back     amp   amp myvar  as swing from dual    Produces   old   1  select  back     amp   amp myvar  from dual new   1  select  back   amp   forth  from dual  SWING -------------- back   amp   forth   If you want to use a different escape character   set define on set escape      define myvar  forth  select  back    amp   amp 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

User · Answer

If you are using sql plus then I think that you need to issue the command   SET SCAN OFF

User · Answer

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

User · Answer

You can insert such an string as  J     amp     Construction   It works fine   insert into table name  col name  values  J     amp     Construction

User · Answer

SET ESCAPE ON  INSERT VALUES  J  amp J Construction   INTO custnames     Untested  don t have an Oracle box at hand and it has been a while

User · Answer

INSERT INTO TEST TABLE VALUES  Jonhy  s Sport  amp      Fitness     This query s output   Jonhy s Sport  amp  Fitness

User · Answer

Stop using SQL Plus  I highly recommend PL SQL Developer  it s much more than an SQL tool   p s  Some people prefer TOAD

[sql] How to insert a string which contains an "&"

Examples related to sql

Examples related to oracle

Examples related to escaping

Examples related to sqlplus