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.
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.
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.
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;
SQL Functions, (Sum, to_date, to_char etc)