How-to: Escape characters

How to deal with special characters. ( & ' _ % )

n.b. If you are writing dynamic SQL do not attempt to 'purify' user input by adding escape characters, use a properly parameterised query instead.

Quote character

To escape quotation marks, use two quotes for every one you want displayed:

SQL> SELECT 'The game is done! ''I''ve won, I''ve won!'' quoth she, and whistles thrice.' AS demo FROM DUAL;

The game is done! 'I've won, I've won!' quoth she, and whistles thrice.

Wildcard characters

The wildcard character _ is used to match one character
The wildcard character % is used to match any characters. These wildcards can be escaped in SQL:

SET ESCAPE '\';
SELECT name FROM emp
WHERE id LIKE '%\%%';

Ampersand (&) characters in SQL*Plus

The & symbol is the default SQL*Plus substitution variable prefix, if you need to use an actual & in an SQL statement, then the SQL*Plus DEFINE setting can change the prefix character to something else:

SET DEFINE ~
SELECT 'Profit & Loss' FROM dual;

Starting a new SQL*Plus session will reset the default (&) escape character.

Other methods:

Set an escape character and then escape the &:

SET ESCAPE '\'
SELECT '\&something' FROM dual;

Don't scan for substitution variables:

SET SCAN OFF
SELECT '&something' x FROM dual;