Disable special character & in Oracle sql statement

If you issue
SQL> select '&1' from dual;
Enter value for 1: dummy
old   1: select '&1' from dual
new   1: select 'dummy' from dual
dummy
However, I would like to get:
&2
instead of dummy, so disable the special charcter & in this sql statement.
jl66Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
SQL> set define off

then run your query





0
 
k_murli_krishnaConnect With a Mentor Commented:
You can do the following alternative ways as well:
SELECT chr(ascii('&')) || '1' FROM dual;
Try also:
SELECT "&" || '1' FROM dual;

Just like single quote ' is specific escape character for ' i.e. you will use it as:
SELECT 'gain''s' FROM dual;
Back slash (\) is the generic escape character in Oracle.
0
 
k_murli_krishnaConnect With a Mentor Commented:
You can use REPLACE and TRANSLATE functions to substitute a harmless and unused character in place of & and then substitute back while using the result OR if used in WHERE condition, then compare with constant/literal value also containing substituted character.
0
All Courses

From novice to tech pro — start learning today.