Inserting the '&' symbol in a string
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 ONAlternatively, 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 offThen 'insert into test (data) values ('Bill & Ben');' inserts one row -- easy!
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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.