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.? |
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
