Escaping ampersand character in SQL string

Instead of

node_name = 'Geometric Vectors \& Matrices'

use

node_name = 'Geometric Vectors ' || chr(38) || ' Matrices' 

38 is the ascii code for ampersand, and in this form it will be interpreted as a string, nothing else. I tried it and it worked.

Another way could be using LIKE and an underline instead the '&' character:

node_name LIKE 'Geometric Vectors _ Matrices' 

The chance that you'll find some other record too, which is different in only this one character, is quite low.

Escape is set to \ by default, so you don't need to set it; but if you do, don't wrap it in quotes.

Ampersand is the SQL*Plus substitution variable marker; but you can change it, or more usefully in your case turn it off completely, with:

set define off

Then you don't need to bother escaping the value at all.

You can use

set define off

Using this it won't prompt for the input

straight from oracle sql fundamentals book

SET DEFINE OFF
select 'Coda & Sid' from dual;
SET DEFINE ON

how would one escape it without setting define.

In order to escape & you can try following ways:-

1. set scan off
2. set define off
3. set escape on then replace & by\&
4. replace & by &&

One of them should work at least.

additionally if you are using PL/SQL developer then there is & icon in the bottom of SQL window please go there and disable it. Note in the older version this option is not present.

Also make sure set define off is written at the very beginning of the script.