The Oracle (tm) Users' Co-Operative FAQ

How do I embed the ampersand (&) as part of the literal text I use in an SQL statement ?


Author's name: Michael Haynes

Author's Email: mhaynes@ee.net

Date written: January 2nd, 2002

Oracle version(s): SQL*Plus 3.3.2 and 8.1.6

What can be done to allow the use of a literal ampersand within a SQL statement for INSERT, SELECT, etc.?

Back to index of questions


If you attempt to include an ampersand character (&) in a string within a SQL statement in SQL*Plus it is interpreted as the beginning of a variable name.  For example:
 

SQL> insert into companies values ('A & P');
Enter value for p: blah
old   1: insert into companies values ('A & P')
new   1: insert into companies values ('A blah')

1 row created.

To turn off this manner of interpreting the ampersand, it must be at the end of a string.  Thus, to handle the insert shown above properly, do the following:
 

SQL> insert into companies values ('A &'||'P');

1 row created.

SQL> select * from companies;

COMPANY_NAME
--------------------------------------------------------------------------------
A blah
A & P

The same syntax can be used to include a literal ampersand in other types of statements (SELECT, UPDATE, etc.) within SQL*Plus.


Further reading: N/A


Back to top

Back to index of questions