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.
Related Articles
- Insert Text Literal and Concatenation in Oracle SQL
- Using Character Map (charmap) to Type and Input Special Characters and Accents in Windows Vista
- Oracle EXP-00091 Error When Export Database
- Check Oracle Version
- Firefox 3.1 Beta Smart Location Bar Enhancement: Special Characters to Restrict Searching & Smart Keyword to Search from Specific Website
- Remove or Trim First or Last Few Characters in MySQL Database with SQL
- Change Oracle Database User Password
- IMP-00016 Required Character Set Conversion Not Supported Error when Import to Oracle Database
- ORA-01502 Oracle Index in Unusable State
- You Are Not Authorized to Execute SQL Queries in vBulletin Maintenance










































September 26th, 2008 00:28
Thank you very much – I’m a SQL Server gent with an Oracle source and I was going nuts!
September 17th, 2008 20:44
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
July 13th, 2007 19:27
[...] about Escape Characters. Escape Characters Oracle Wildcards Wildcard Characters Escape Character How to Escape Characters in Oracle PL/SQL Queries