Oracle escaping special characters

In a nutshel

Turn of special meaning of chars

-- The ampersand has no special meaning anymore
SET DEFINE OFF;  
SELECT 'V&D' FROM dual;

Escaping characters in LIKE

-- The meaning of the underscore is escaped
SET ESCAPE ON;
SELECT * FROM maxtable WHERE tablename LIKE 'A\\_%' ESCAPE '\\';

Other Examples

Examples - Supressing special chars

-- Special chars are now treated as normal characters
SET DEFINE OFF;  

-- All characters are in the output
SELECT 'V&D' FROM dual;
SELECT 'V\&D' FROM dual;

-- Escape characters works as expected
SELECT * FROM maxmessages WHERE value LIKE '%\%%' ESCAPE '\';

-- No results...there are no tablenames which start with letter A and a backslash
SELECT * FROM maxtable WHERE tablename LIKE 'A\_%';

-- Runs into an error
SELECT * FROM maxmessages WHERE value LIKE '%\\%%' ESCAPE '\\';

Examples - Escape character

-- Set dash (=default) as an escape character
SET ESCAPE ON;
-- or explicitly set backslash as an escape character
SET ESCAPE \;

-- Escape char works as expected
SELECT 'V\&D' FROM dual;

-- Underscore and Percentage are escaped as expected
SELECT * FROM maxtable WHERE tablename LIKE 'A\\_%' ESCAPE '\\';
SELECT * FROM maxmessages WHERE value LIKE '%\\%%' ESCAPE '\\';

-- Both statemenst have the same result (escape char has no influence)
SELECT * FROM maxtable WHERE tablename LIKE 'A_%';
SELECT * FROM maxtable WHERE tablename LIKE 'A\_%';
-- Special chars are now handled as normal characters
SET DEFINE OFF;  

-- All characters are in the output
SELECT 'V&D' FROM dual;
SELECT 'V\&D' FROM dual;

-- Escape characters works as expected
SELECT * FROM maxmessages WHERE value LIKE '%\%%' ESCAPE '\';

-- No results...there are no tablenames which start with letter A and a backslash
SELECT * FROM maxtable WHERE tablename LIKE 'A\_%';

-- Runs into an error
SELECT * FROM maxmessages WHERE value LIKE '%\\%%' ESCAPE '\\';

-- Variable substitution is performed
SELECT 'V&D' FROM dual;

-- Runs into an error
SELECT * FROM maxmessages WHERE value LIKE '%\%%' ESCAPE '\';