Search the Site
Sponsors
How to use the ampersand (&) symbol in SQL statements as a literal value
This note provides two ways for you to use the ampersand symbol, "&", as a literal value in an Oracle SQL statement.
This page is filed under keyword(s): oracle.
Because the ampersand symbol, "&", denotes the start of a variable, you might be caught off guard when you are trying to use it as a literal value. For example:
SQL> select 'I like fish & chips' as x from dual; Enter value for chips: old 1: select 'I like fish & chips' as x from dual new 1: select 'I like fish ' as x from dual X ------------ I like fish 1 row selected.
Of course, this is not what what you intended; you meant for the literal string to appear as "I like fish & chips". There are two ways for you to get around this problem. The first method is to make sure the ampersand symbol is at the end of a string, thus requiring you to break up your sentence in one of the two following ways.
SQL> select 'I like fish &' || ' chips' as x from dual;
X
-------------------
I like fish & chips
1 row selected.
SQL> select concat('I like fish &',' chips') as x from dual;
X
-------------------
I like fish & chips
1 row selected.
The second method is to escape the ampersand symbol in the following manner.
SQL> set escape on; SQL> select 'I like fish \& chips' as x from dual; X ------------------- I like fish & chips 1 row selected.
Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.
This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 20 Nov 2009
