Answer

Inserting the '&' symbol in a string

Published: 29 Jan 2003

How do I insert the '& ' symbol in a string? 

 
Insert into Tablea (Columna) values ('Tony & Laura')
Oracle does not like the &.

Oracle has no problem with the '&' symbol. For instance, I was able to insert a line such as above using a Perl program. The problem you are most likely experiencing is that SQL*Plus does not like the '&' symbol! This is because SQL*Plus sees this symbol as defining a variable. So if I issue the following in SQL*Plus: 

ORA9I SQL> insert into test values ('this & that');
Enter value for that:
You can see that SQL*Plus is assuming that the word "that" is a variable. Since SQL*Plus does not have this variable defined, it is asking for the value of this variable. To be able to insert this character into a table in SQL*Plus, you need to "escape" it's meaning. This can be done with the escape character, which defaults to ''. You will first need to turn on the escape functionality in SQL*Plus: 
set escape ON
Alternatively, you can change the escape character: 
set escape '^'
So use the escape character to insert the '&' symbol as follows: 
ORA9I SQL> set escape ON
ORA9I SQL> show escape
escape "" (hex 5c)
ORA9I SQL> insert into test values ('this & that');

1 row created.

ORA9I SQL> select * from test;

VAL
--------------------------------------------------
this & that

Reader Tony Bunosso adds:

In addition to the method you posted, you can also perform the insert in the following manner:

 insert into test values ('Luke '||'&'||' Laura');

Reader Peter Robson adds:

Brian Peasland gave an un-necessarily complex answer to the question of how to insert an '&' into a text field. The solution is solved with one set command: 

set scan off
Then 'insert into test (data) values ('Bill & Ben');' inserts one row -- easy!

Brian responds:

It always goes to show that there is normally more than one way to perform the same task! It doesn't mean that one way is correct and the other way is wrong. Just different. And typically each way will have their own strengths and weaknesses.

For More Information


Dig Deeper on Oracle database administration