How does one escape special characters when writing SQL queries?

Escape quotes

Use two quotes for every one displayed. Examples:

SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;

 TEXT

 --------------------

 Franks's Oracle site

 SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;

 TEXT

 ----------------

 A 'quoted' word.

 SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;

 TEXT

 -------------------------

 A ''double quoted'' word.

Use Q expression:

SQL> SELECT q'[Frank's Oracle site]' AS text FROM DUAL;

 TEXT

 -------------------

 Frank's Oracle site

 SQL> SELECT q'[A 'quoted' word.]' AS text FROM DUAL;

 TEXT

 ----------------

 A 'quoted' word.

 SQL> SELECT q'[A ''double quoted'' word.]' AS text FROM DUAL;

 TEXT

 -------------------------

 A ''double quoted'' word.

Escape wildcard characters

The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:

SELECT name FROM emp

 WHERE id LIKE '%/_%' ESCAPE '/';

SELECT name FROM emp

 WHERE id LIKE '%\%%' ESCAPE '\';

Escape ampersand (&) characters in SQL*Plus

When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:

SET DEFINE ~

SELECT 'Laurel & Hardy' FROM dual;

Other methods:

Define an escape character:

SET ESCAPE '\'

SELECT '\&abc' FROM dual;

Don't scan for substitution variables:

SET SCAN OFF

SELECT '&ABC' x FROM dual;

Another way to escape the & would be to use concatenation, which would not require any SET commands -

SELECT 'Laurel ' || '&' || ' Hardy' FROM dual;

Use the 10g Quoting mechanism:

Syntax

 q'[QUOTE_CHAR]Text[QUOTE_CHAR]'

 Make sure that the QUOTE_CHAR followed by an ' doesn't exist in the text.

SELECT q'{This is Orafaq's 'quoted' text field}' FROM DUAL;