How to Escape Characters in Oracle PL/SQL Queries

Oracle databases reserve some special characters with specific meaning and purpose within Oracle environment. These reserved characters include _ (underscore) wild card character which used to match exactly one character, % (percentage) which used to match zero or more occurrences of any characters and ‘ (apostrophe or quotation mark) which used to mark the value supplied. These special characters will not be interpreted literally when building SQL query in Oracle, and may caused error in results returned especially when performing string search with LIKE keyword. To use these characters so that Oracle can interpret them literally as a part of string value instead of preset mean, escape character has to be assigned.

Oracle allows the assignment of special escape characters to the reserved characters in Oracle can be escaped to normal characters that is interpreted literally, by using ESCAPE keyword.

For example, to select the name of guests with _ (underscore) in it, use the following statement:

SELECT guest_name FROM guest_table WHERE name LIKE ‘%\_%’ ESCAPE ‘\’;

Without specifying the \ (backslash) as escape clause, the query will return all guest names, making the unwanted results problem.

The above syntax will not work on ‘ (quote). To escape this quotation mark and to display the quote literally in string, insert another quote (total 2 quotes) for every quote that want to be displayed. For example:

SELECT ‘This will display line with quote’’s word.’ FROM temp_table;
SELECT ‘This will display ””double quoted”” word.’ FROM temp_table;

will return the following respectively:

This will display line with quote’s word.
This will display ”double quoted” word.

Share and contribute or get technical support and help at My Digital Life Forums.



One Response to “How to Escape Characters in Oracle PL/SQL Queries”

  1. Escape Character in MySql - Discussweb IT Community - Web Development, Software Programming, SEO, Quality Assurance, 3D, Web Hosting and more...
    July 13th, 2007 19:27
    1

    [...] about Escape Characters. Escape Characters Oracle Wildcards Wildcard Characters Escape Character How to Escape Characters in Oracle PL/SQL Queries

Leave a Reply

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe without commenting


Custom Search

New Articles

Incoming Search Terms for the Article

oracle escape character - sql escape character - oracle escape - oracle sql escape - oracle escape characters - pl/sql escape character - oracle sql escape character - oracle escape & - sql escape - plsql escape - oracle escape apostrophe - oracle escape quote - pl sql escape character - escape character in oracle - oracle escape char - escape character oracle - sql escape characters - escape characters in Oracle - escape character in PL/SQL - escape character in sql - oracle sql escape characters - plsql escape character - pl/sql escape - oracle escaping ' - oracle string escape - pl/sql escape characters - plsql escape characters - SQL escape char - oracle sql escape & - oracle escape character ' - escape characters in SQL - escape character pl/sql - pl/sql escape & - oracle escape string - escape in oracle - escaping & in oracle - pl sql escape characters - sql reserved characters - Escaping & in sql - SQL escape & - escape sql - oracle escaping - escape character in plsql - oracle escape ' - oracle & escape - how to escape & in sql - escape characters sql - oracle plsql escape character - plsql escape & - Oracle SQL escape -