Skip navigation.
Home » Oracle FAQ Knowledge Base » SQL and PL/SQL » SQL

How does one escape special characters when writing SQL queries?

Submitted by admin on Wed, 2004-08-04 13:53

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;

»

User defined quoted characters is supported in Oracle10g

Submitted by Velu Natarajan (not verified) on Mon, 2006-04-17 06:56.

Oracle10g allows you to define your own string delimiters. Any character that is not present in the string can be defined as:

--Previous syntax
dbms_output.put_line('Hi Ram''s!');

--New syntax
dbms_output.put_line(q'#Hi Ram's!#');
dbms_output.put_line(q'$Hi Ram's!$');

»

What about dynamic SQL?

Submitted by Tom Myers (not verified) on Wed, 2006-08-23 11:23.

When passing string values from a user interface into an Oracle stored procedure, for use in a dynamicly constructed SQL query, they must be "escaped" to insure the query isn't broken or worse, provide a security whole.

Your examples don't cover this situation, nor how to escape characters in a simple where clause such as:

select * from employee where last_name = vUiInputValueVariable;

Without an escape, a string such as '%';delete from employee
could wipe out the table!

Of course writting dynamic SQL such as this is very bad practice, but its possible if the input string is properly escaped.

One online Oracle documentation source for version 9.2(http://www.lc.leidenuniv.nl/awcourse/oracle/text.920/a96518/cqspcl.htm) indicates braces ( {} ) or a backslash ( \ ) can be used to escape entire strings or characters. However those techniques do not work for some reason on our Oracle 9.2 installation.

I hope this info is helpful.

»