Using ampersands (&) without variable substitution in Oracle either direct or with SQL*Plus

Oracle uses the ampersand (&) symbol within a string to indicate a substitution variable. But what if you want to use an ampersand as part of a regular string such as 'Bob & Sons'?

There are two solutions that I know of and which one you use depends on the context in which you are running your SQL:

Dealing with the ampersand on an SQL level

To turn off interpreting the ampersand as a substitution variable it must be at the end of a string:

insert into companies values ('Bob &' || ' Sons');

Dealing with the ampersand when using SQL*Plus

To disable Oracle's variable substitution and therefore return & to the pool of standard characters you need to tell SQL*Plus to disable it:

set define off

Add that to the top of your Oracle SQL script or type it manually at the SQL*Plus prompt.

These tips plus many more can be found within the rather useful The Oracle (tm) Users' Co-Operative FAQ.

Technorati Tags: Oracle, Database, Ampersand, Variable Substitution, SQLPlus, Andrew Beacock