Somebody wanted to understand why you can backquote a single wildcard operator (that’s the underscore
_ character) in MySQL, but can’t in Oracle. The answer is you can in Oracle when you know that you required an additional clause.
While I prefer using regular expression resolution, the
LIKE operator is convenient. Here’s an example of backquoting an underscore in MySQL, where it looks for any string with an underscore anywhere in the string:
SELECT common_lookup_type FROM common_lookup WHERE common_lookup_type LIKE '%\_%';
You can gain the same behavior in Oracle by appending the
ESCAPE '\' clause, like this:
SELECT common_lookup_type FROM common_lookup WHERE common_lookup_type LIKE '%\_%' ESCAPE '\';
ESCAPE '\' clause is one of those Oracle details that often gets lost. It only works when the SQL*Plus
ESCAPE parameter is set to
ESCAPE parameter default value is a backslash. and when the ESCAPE parameter is enabled a statement like this raises the following exception:
ERROR at line 3: ORA-01425: escape CHARACTER must be CHARACTER string OF LENGTH 1
If you drop the
ESCAPE '\' clause with the
ESCAPE parameter enabled it will return all rows from the table not just those strings with an underscore in the string. Hope this helps if need to look for an underscore in a table.