How to insert a string which contains an "&"

I keep on forgetting this and coming back to it again! I think the best answer is a combination of the responses provided so far.

Firstly, & is the variable prefix in sqlplus/sqldeveloper, hence the problem - when it appears, it is expected to be part of a variable name.

SET DEFINE OFF will stop sqlplus interpreting & this way.

But what if you need to use sqlplus variables and literal & characters?

• You need SET DEFINE ON to make variables work
• And SET ESCAPE ON to escape uses of &.

e.g.

set define onset escape ondefine myvar=/forthselect 'back\\ \& &myvar' as swing from dual;

Produces:

old   1: select 'back\\ \& &myvar' from dualnew   1: select 'back\ & /forth' from dualSWING--------------back\ & /forth

If you want to use a different escape character:

set define onset escape '#'define myvar=/forthselect 'back\ #& &myvar' as swing from dual;

When you set a specific escape character, you may see 'SP2-0272: escape character cannot be alphanumeric or whitespace'. This probably means you already have the escape character defined, and things get horribly self-referential. The clean way of avoiding this problem is to set escape off first:

set escape offset escape '#'

If you are doing it from SQLPLUS use

SET DEFINE OFF  

to stop it treading & as a special case

An alternate solution, use concatenation and the chr function:

select 'J' || chr(38) || 'J Construction' from dual;