CODEWITHSUNDEEP
sqloracle-databaseescapingsqlplus
Andrew Hampton
Andrew Hampton

Reputation: 1742

How to insert a string which contains an "&"

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.

Upvotes: 52

Views: 260977

Answers (14)

Alberto Cerqueira
Alberto Cerqueira

Reputation: 1419

Look, Andrew:

"J&J Construction":

SELECT CONCAT('J', CONCAT(CHR(38), 'J Construction')) FROM DUAL;

Upvotes: 0

aemre
aemre

Reputation: 2570

INSERT INTO TEST_TABLE VALUES('Jonhy''s Sport &'||' Fitness')

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

Upvotes: 6

lokesh kumar
lokesh kumar

Reputation: 981

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

insert into table_name (col_name) values('J'||'&'||'Construction');

Upvotes: 5

C. J.
C. J.

Reputation: 171

An alternate solution, use concatenation and the chr function:

select 'J' || chr(38) || 'J Construction' from dual;

Upvotes: 17

Hans
Hans

Reputation: 1372

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

Upvotes: 5

tardate
tardate

Reputation: 16774

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

Upvotes: 73

Osama Al-Maadeed
Osama Al-Maadeed

Reputation: 5695

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

p.s. Some people prefer TOAD.

Upvotes: 0

oglester
oglester

Reputation: 6655

The correct syntax is

set def off;
insert into tablename values( 'J&J');

Upvotes: 8

Mike Dimmick
Mike Dimmick

Reputation: 9802

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.

Upvotes: 4

stjohnroe
stjohnroe

Reputation: 3206

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

SET SCAN OFF

Upvotes: 0

hamishmcn
hamishmcn

Reputation: 7981

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

Upvotes: 4

Andrew Hampton
Andrew Hampton

Reputation: 1742

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.

Upvotes: 2

Roel
Roel

Reputation: 19642

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)

Upvotes: 0

hamishmcn
hamishmcn

Reputation: 7981

If you are doing it from SQLPLUS use 

SET DEFINE OFF

to stop it treading & as a special case

Upvotes: 22

Related Questions