MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

SQL Like Comparisons

with 4 comments

SQL tidbits are always valuable and highly searched for by newbies (as opposed to reading the SQL documentation). Sometimes we seasoned SQL developers take for granted little things like when a single- or multiple-character wildcard comparison works. It seems we know what newbies don’t. That you need a wildcard comparison operator not simply and equality comparison operator.

The question posed to me was, “Why doesn’t my wildcard comparison work?” Here’s a simplified example of their question.

SELECT 'Valid' AS "Test"
FROM    dual
WHERE  'Treat' = 'Tre_t'
OR     'Treet' = 'Tre_t';

Naturally, the answer is that the equality operator compares the strings based on their exact match (character sensitively in Oracle and character insensitively in MySQL). It needs to be rewritten by replacing the equals (=) comparison operator with the LIKE comparison operator. The following query does that:

SELECT 'Valid' AS "Test"
FROM    dual
WHERE  'Treat' LIKE 'Tre_t'
OR     'Treet' LIKE 'Tre_t'

The same behavior exists for the multiple-character wildcard (%). I hope this helps those looking for this answer.

Written by maclochlainn

July 13th, 2013 at 1:12 pm

4 Responses to 'SQL Like Comparisons'

Subscribe to comments with RSS or TrackBack to 'SQL Like Comparisons'.

  1. Nice tidbit.
    I would further add that in the special case of matching the underscore, or per cent sign, one must be sure to precede that special character with a backslash.

    For example, to match “m_sql” and to not pick up “mysql“, one would write: like “m\_sql

    Data munger

    13 Jul 13 at 6:51 pm

  2. I’m not sure why you feel the backslash is necessary in a SQL statement? This works in both Oracle or MySQL.

    mysql> SELECT 'Valid' AS "Test"
        -> FROM    dual
        -> WHERE  'Tre_t' LIKE 'Tre_t';
    +-------+
    | Test  |
    +-------+
    | Valid |
    +-------+
    1 row in set (0.00 sec)

    maclochlainn

    13 Jul 13 at 10:18 pm

  3. I Think Data Munger’s comment should be understodd the way the escaping is required AFTER the LIKe comparisor in case you wnat to find exact mathces with characters that aare also wildcard characters ("_" and "%").

    See

    SELECT 'Valid' AS "Test" FROM  DUAL WHERE  'TreXt' LIKE 'Tre_t'; -- returns 'Valid'
    SELECT 'Valid' AS "Test" FROM  DUAL WHERE  'TreXt' LIKE 'Tre\_t'; -- returns empty set

    In 'TreXt' the 'X' matches the the "_" in the wildcard’ed comparison string but not "_" as a literal (what is specified as "\_").

    Peter Laursen

    14 Jul 13 at 7:48 am

  4. Awesome, that makes perfect sense. Thanks, I missed it.

    maclochlainn

    14 Jul 13 at 9:17 am

Leave a Reply