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.
select * from I2TM_PSTL.pstl_cd_t L where CITY_NAME in (‘AT \& T’)
How to resolve this
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;