Home / SQL Statements / Question No: 223

Operators such as = and != are useful for finding values that are equal to or not equal to a specific exact comparison value. When it's necessary to find values based on similarity instead, a pattern match is useful. To perform a pattern match, use value LIKE 'pattern', where value is the value you want to test and 'pattern' is a pattern string that describes the general form of values that you want to match.

Patterns used with the LIKE pattern-matching operator can contain two special characters (called "metacharacters" or "wildcards") that stand for something other than themselves:

• The '%' character matches any sequence of zero or more characters. For example, the pattern 'a%' matches any string that begins with 'a', '%b' matches any string that ends with 'b', and '%c%' matches any string that contains a 'c'. The pattern '%' matches any string, including empty strings.
• The '_' (underscore) character matches any single character. 'd_g' matches strings such as 'dig', 'dog', and 'd@g'. Because '_' matches any single character, it matches itself and the pattern 'd_g' also matches the string 'd_g'.

A pattern can use these metacharacters in combination. For example, '_%' matches any string containing at least one character.

LIKE evaluates to NULL if either operand is NULL, but any non-NULL literal value matches itself. Likewise, a function call that produces a non-NULL value matches itself (with one exception). Thus, the following expressions evaluate as true:

'ABC' LIKE 'ABC'
column_name LIKE column_name
VERSION() LIKE VERSION()

The exception is that different invocations of the RAND() random-number function might return different values, even within the same query:

mysql> SELECT RAND(), RAND();
+------------------+------------------+
| RAND()           | RAND()           |
+------------------+------------------+
| 0.15430032289987 | 0.30666533979277 |
+------------------+------------------+

As a result, the expression RAND() LIKE RAND() normally will be false.

LIKE performs a non-binary comparison if both operands are non-binary strings; otherwise, the comparison is binary:

mysql> SELECT 'ABC' LIKE 'abc', 'ABC' LIKE BINARY 'abc';
+------------------+-------------------------+
| 'ABC' LIKE 'abc' | 'ABC' LIKE BINARY 'abc' |
+------------------+-------------------------+
|                1 |                       0 |
+------------------+-------------------------+

To invert a pattern match, use NOT LIKE rather than LIKE:

mysql> SELECT 'ABC' LIKE 'A%', 'ABC' NOT LIKE 'A%';
+-----------------+---------------------+
| 'ABC' LIKE 'A%' | 'ABC' NOT LIKE 'A%' |
+-----------------+---------------------+
|               1 |                   0 |
+-----------------+---------------------+

MySQL, unlike some other database systems, allows use of LIKE with non-string values. This can be useful in some cases. For example, the expression d LIKE '19%' is true for date values d that occur during the 1900s. MySQL evaluates such comparisons by converting non-string values to strings before performing the pattern match.

It's possible to specify the pattern in a LIKE expression using a table column. In this case, the actual pattern that a value is compared to can vary for every row of a result set. The following table has one column containing patterns and another column that characterizes the type of string each pattern matches:

mysql> SELECT pattern, description FROM patlist;
+---------+--------------------------------+
| pattern | description                    |
+---------+--------------------------------+
|         | empty string                   |
| _%      | non-empty string               |
| _ _ _   | string of exactly 3 characters |
+---------+--------------------------------+

The patterns in the table can be applied to specific values to characterize them:

mysql> SELECT description, IF('' LIKE pattern,'YES','NO') FROM patlist;
+--------------------------------+--------------------------------+
| description                    | IF('' LIKE pattern,'YES','NO') |
+--------------------------------+--------------------------------+
| empty string                   | YES                            |
| non-empty string               | NO                             |
| string of exactly 3 characters | NO                             |
+--------------------------------+--------------------------------+

mysql> SELECT description, IF('abc' LIKE pattern,'YES','NO') FROM patlist;
+--------------------------------+-----------------------------------+
| description                    | IF('abc' LIKE pattern,'YES','NO') |
+--------------------------------+-----------------------------------+
| empty string                   | NO                                |
| non-empty string               | YES                               |
| string of exactly 3 characters | YES                               |
+--------------------------------+-----------------------------------+

mysql> SELECT description, IF('hello' LIKE pattern,'YES','NO') FROM patlist;
+--------------------------------+-------------------------------------+
| description                    | IF('hello' LIKE pattern,'YES','NO') |
+--------------------------------+-------------------------------------+
| empty string                   | NO                                  |
| non-empty string               | YES                                 |
| string of exactly 3 characters | NO                                  |
+--------------------------------+-------------------------------------+

To match a pattern metacharacter literally, escape it by preceding it by a backslash:

mysql> SELECT 'AA' LIKE 'A%', 'AA' LIKE 'A%', 'A%' LIKE 'A%';
+----------------+-----------------+-----------------+
| 'AA' LIKE 'A%' | 'AA' LIKE 'A%' | 'A%' LIKE 'A%' |
+----------------+-----------------+-----------------+
|              1 |               0 |               1 |
+----------------+-----------------+-----------------+

mysql> SELECT 'AA' LIKE 'A_', 'AA' LIKE 'A_', 'A_' LIKE 'A_';
+----------------+-----------------+-----------------+
| 'AA' LIKE 'A_' | 'AA' LIKE 'A_' | 'A_' LIKE 'A_' |
+----------------+-----------------+-----------------+
|              1 |               0 |               1 |
+----------------+-----------------+-----------------+

To specify a given character as the escape character, use an ESCAPE clause:

mysql> SELECT 'AA' LIKE 'A@%' ESCAPE '@', 'A%' LIKE 'A@%' ESCAPE '@';
+----------------------------+----------------------------+
| 'AA' LIKE 'A@%' ESCAPE '@' | 'A%' LIKE 'A@%' ESCAPE '@' |
+----------------------------+----------------------------+
|                          0 |                          1 |
+----------------------------+----------------------------+