home

Search A2Z 24

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.

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 '/';
SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;

TEXT
-------------------------
A ''double quoted'' word.
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;
loading...

Share your thoughts!

Login as a member to access comment posting block !! click-here

Thoughts From Other Users (0)

No Comments

Reveal Modal Goodness

This is a default modal in all its glory, but any of the styles here can easily be changed in the CSS.

This is just a simple modal with the default styles, but any type of content can live in here. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi quis sem vel enim eleifend tristique. Etiam tincidunt faucibus pharetra.

×