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.

3 Responses to “How to Escape Characters in Oracle PL/SQL Queries”

  1. Charles Asbornsen
    September 26th, 2008 00:28
    3

    Thank you very much – I’m a SQL Server gent with an Oracle source and I was going nuts! :)

  2. sam33
    September 17th, 2008 20:44
    2

    salut j’ai le mm pb ! si je fait une recherche sur le ‘_’ seul sa marche mais si v je chercher des mots avec le ‘_’
    exmple:
    SELECT * from Test WHERE nom LIKE ‘1%\_%test’ ESCAPE ‘\’;
    sa me renvoie :
    1_test
    10_test ??
    or que normalement sa devait rendre que:
    1_test ??
    je comprend pas

  3. 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 to My Digital Life Comments RSS feed to receive notification of latest comments posted.

New Articles

Incoming Search Terms for the Article

escape character in oracle - oracle escape character - pl sql escape character - oracle escape - sql escape - escape sequence in oracle - oracle sql escape - oracle apostrophe escape - oracle escape & - oracle string escape - oracle escape keyword - oracle sql escape character - pl/sql escape - escape characters in oracle - sql escape character - escape character in pl sql - oracle escape ' - oracle & character - oracle escape sequence - escaping ' in sql - ESCAPE CHARACTER ORACLE - ESCAPE oracle - PLSQL escape - oracle escape string - oracle escape apostrophe - oracle string escape character - oracle escape characters - oracle sql & character - pl sql escape - plsql escape character - oracle quote escape - pl/sql escape character - oracle ' escape - SQL ORACLE escape - oracle scape character - escape in oracle query - oracle pl sql escape character - pl sql escape single quote - pl sql escape characters - PL SQL ESCAPE QUOTE - escape plsql - escape sql - pl/sql escape characters - escape character in PL/SQL - escape keyword in oracle - escape in oracle - oracle ' character - oracle escape syntax - escape character in oracle sql - oracle real escape string - escape in pl sql - oracle pl sql escape - oracle escape char - oracle single quote escape - pl sql special characters in string - escaping & in oracle - escape ' in oracle - sql ' character - escape oracle string - escape sequence in oracle sql - sql escape & - how to serch name in oracle query - oracle escape character in PL SQL - pl-sql escape character - pl sql encode - escape characters in pl sql - escape character in oracle pl sql - pl sql escape & - pl sql quotes within quotes - escape pl sql - escape character in pl sql oracle - escape sequence for & in oracle - escape & in sql - oracle escape single quote - oracle sql escape special characters -