MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Single Wildcard Operator

with 2 comments

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 '\';

The ESCAPE '\' clause is one of those Oracle details that often gets lost. It only works when the SQL*Plus ESCAPE parameter is set to OFF.

The SQL*Plus 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.

Written by maclochlainn

June 22nd, 2012 at 12:41 am

2 Responses to 'Single Wildcard Operator'

Subscribe to comments with RSS or TrackBack to 'Single Wildcard Operator'.

  1. […] Michael McLaughlin is discussing the single wildcard operator with respect to MySQL and Oracle. […]

  2. REXEXP_LIKE, in Oracle 10G SQL and later, provides another way to match underscore (and other regular expressions including many Perl-influenced extensions).

    SELECT common_lookup_type
    FROM common_lookup
    WHERE REGEXP_LIKE(common_lookup_type,’_’);

    Mark

    2 Mar 14 at 11:49 pm

Leave a Reply