Oracle – Escaping Wild Card Characters with LIKE operator
In Oracle, % (percentage) and _ (underscore) have a special meaning. These two wild card characters are used with LIKE operator:
% allows matching any string of any length (including zero length)
_ allows matching a single character
Example #1
SELECT dname FROM dept WHERE dname LIKE 'A%';
will return all Department names starting with A like ACCOUNTS, ARTS, ARCHEOLOGY, A%SCEINCES etc i.e.
Output:
DNAME
————-
ACCOUNTS
ARTS
ARCHEOLOGY
A%SCEINCES
Example #2
In the above statement, if we change A% to A%S i.e.
SELECT dname FROM dept WHERE dname LIKE 'A%S';
The result would only return ACCOUNTS, ARTS, A%SCEINCES but not ARCHEOLOGY as it doesn’t end with S i.e.
Output:
DNAME
————-
ACCOUNTS
ARTS
A%SCEINCES
While searching for a string in Oracle, sometimes one might want to use % and _ literally. i.e. there could be cases when you’d like to do a start with search using an input/search string containing % or _. The expected output should return only those strings that actually contain these character(s) at the specified places and not make use of the “special meaning” attached to them e.g. in Example #2 above, i might want to fetch only department names starting with A%S i.e. only A%SCEINCES in this case.
This can be obtained by escaping the wild card characters by defining an escape character:
Example #3:
SELECT dname FROM dept WHERE dname LIKE 'A\%S%' ESCAPE '\';
Output:
DNAME
————-
A%SCEINCES
In Example #3, we knew the search string, but in a practical scenario, it would be a dynamic input entered by the user of the application/program using this query. For this, all appearances of % and _ will have to be escaped. For this, a generic function can be written that will take the input string, escape all occurrences of % and _ (with the escape character also sent as input) and return the escaped string that can be further used by the final search query.
Function:
-- This function is used to escape '%' and '_' for search CREATE OR REPLACE FUNCTION fnc_escape_wild_char ( iv_String IN VARCHAR2, iv_Escape_Char IN VARCHAR2 ) RETURN VARCHAR2 AS lv_String VARCHAR2(4000) := iv_String; BEGIN IF (INSTR(iv_String, '%') > 0) THEN lv_String := REPLACE(lv_String, '%', iv_Escape_Char || '%'); END IF; IF (INSTR(iv_String, '_') > 0) THEN lv_String := REPLACE(lv_String, '_', iv_Escape_Char || '_'); END IF; RETURN lv_String; END fnc_escape_wild_char;
Example #4:
Sample query for using the function:
SELECT fnc_escape_wild_char('A%S', '\') from dual;
Output string will be A\%S
Example #5:
For using the above function with Example #3, the following query can be used. Note that here ‘%’ is appended at the end of the escaped string to get all entries starting with A%S:
SELECT dname FROM dept WHERE dname LIKE ((SELECT fnc_escape_wild_char('A%S', '\') from dual) || '%') ESCAPE '\';
Output will be same as Example #3 i.e.:
DNAME
————-
A%SCEINCES
Any query to be used by escaping % or _ (or both) can be used in a similar fashion.
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
select *
from (select ‘Shri_Ram’ abc from dual) a
where a.abc like ‘%\__%’ escape ‘\’ (double underscore)
Actually i have files names like abc_123_20110704 where 123 are dynamic entries so i want to make a search of all files having name starts with abc and 20110704 where 123 can be any other number.
if i have to select different letter starting with the name, example anu, paul i have to select names starting with a and p through single query.
Hi..
How to display the names which consists the underscore(_) as one of the character in their name. I willing to execute only whose names having underscores not all the employees names ( for eg sri_ram,sri_sai). Can you please sugeest the answer to my query..