49

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.

Mihai Limbășan
  • 64,368
  • 4
  • 48
  • 59
Andrew Hampton
  • 1,632
  • 3
  • 20
  • 29

14 Answers14

72

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 '#'
tardate
  • 16,424
  • 15
  • 50
  • 50
  • np! as I said, I keep on forgetting this, so happy for the chance to record the 'comprehensive answer';-) – tardate Jan 20 '09 at 08:06
  • This [SQL*Plus FAQ](http://www.orafaq.com/wiki/SQL*Plus_FAQ#How_does_one_disable_interactive_prompting_in_SQL.2APlus.3F) provides a similar answer. – DavidRR Nov 16 '12 at 14:15
22

If you are doing it from SQLPLUS use 

SET DEFINE OFF

to stop it treading & as a special case

hamishmcn
  • 7,843
  • 10
  • 41
  • 46
17

An alternate solution, use concatenation and the chr function:

select 'J' || chr(38) || 'J Construction' from dual;
WoodenKitty
  • 6,521
  • 8
  • 53
  • 73
C. J.
  • 171
  • 1
  • 2
8

The correct syntax is

set def off;
insert into tablename values( 'J&J');
oglester
  • 6,605
  • 8
  • 43
  • 63
5

You can insert such an string as 'J'||'&'||'Construction'. It works fine.

insert into table_name (col_name) values('J'||'&'||'Construction');
lokesh kumar
  • 961
  • 1
  • 10
  • 18
5

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' ) )

Hans
  • 1,292
  • 9
  • 7
5
INSERT INTO TEST_TABLE VALUES('Jonhy''s Sport &'||' Fitness')

This query's output : Jonhy's Sport & Fitness

aemre
  • 2,351
  • 2
  • 17
  • 20
4

SET SCAN OFF is obsolete http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a90842/apc.htm

hamishmcn
  • 7,843
  • 10
  • 41
  • 46
4

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.

Mike Dimmick
  • 9,662
  • 2
  • 23
  • 48
2

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.

Andrew Hampton
  • 1,632
  • 3
  • 20
  • 29
0

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

SET SCAN OFF
stjohnroe
  • 3,168
  • 1
  • 27
  • 27
0
SET ESCAPE ON;
INSERT VALUES("J\&J Construction") INTO custnames;

(Untested, don't have an Oracle box at hand and it has been a while)

Roel
  • 19,338
  • 6
  • 61
  • 90
0

Stop using SQL/Plus, I highly recommend PL/SQL Developer it's much more than an SQL tool.

p.s. Some people prefer TOAD.

Osama Al-Maadeed
  • 5,654
  • 5
  • 28
  • 48
0

Look, Andrew:

"J&J Construction":

SELECT CONCAT('J', CONCAT(CHR(38), 'J Construction')) FROM DUAL;
Saravanan Sachi
  • 2,572
  • 5
  • 33
  • 42
Alberto Cerqueira
  • 1,339
  • 14
  • 18