How to use the ampersand (&) symbol in SQL statements as a literal value

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.

2 Replies to “How to use the ampersand (&) symbol in SQL statements as a literal value”

    1. Thanks for your question Sushma. I’m not really seeing what your question is. Are you getting any prompts or error messages with that statement? How are you running this statement?

      When I just ran the following in SQL Developer, it returned “AT & T” as I had expected:

      set escape on;
      select ‘AT \& T’ from dual;

Leave a Reply

Your email address will not be published. Required fields are marked *