DBA Tips Archive for Oracle
Using ESCAPE with LIKE
by Jeff Hunter, Sr. Database Administrator
A question that comes up often is how to use the underscore '_' and percent signs '%' in a query that uses LIKE. The answer is to ESCAPE the underscore and/or percent sign within the LIKE template.
The problem with the underscore and percent character is that they are the default wildcard characters used in SQL using LIKE. For example, if you wanted to retrieve TABLE_NAMES from ALL_TABLES where the table name is like 'EMP_' you might be tempted to try the following query:
SELECT table_name FROM all_tables WHERE table_name LIKE 'EMP_%';
You will find that this query might return not only EMP_NAME, but also EMPLOYEE since the underscore character is being treated as a single character wildcard.
To start the process, you will need to determine what character you would like to use for the ESCAPE keyword. This is typically a slash '\' but any character will work.
SELECT table_name FROM all_tables WHERE table_name LIKE 'EMP\_%' ESCAPE '\' /
Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.