-
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.
-
-
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 ‘Lorel & 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;
-
-
USE the 10g Quoting mechanism:
-
Syntax
-
q‘[QUOTE_CHAR]Text[QUOTE_CHAR]’
-
Make sure that the QUOTE_CHAR doesnt exist IN the text.
-
-
SELECT q‘{This is Orafaq’s ‘quoted’ text FIELD}‘ FROM DUAL;
-