MySQL —

The LIKE Operator

All the previous operators we studied filter against known values. Be it matching one or more values, testing for greater-than or less-than known values, or checking a range of values, the common denominator is that the values used in the filtering are known. But filtering data that way does not always work. For example, how could you search for all products that contained the text anvil within the product name? That cannot be done with simple comparison operators; that's a job for wildcard searching. Using wildcards, you can create search patterns that can be compared against your data. In this example, if you want to find all products that contain the words anvil, you could construct a wildcard search pattern enabling you to find that anvil text anywhere within a product name.

What are Wildcards?

Wildcards are special characters used to match parts of a value.

What is Search Pattern?

Search pattern is a search condition made up of literal text, wildcard characters, or any combination of the two.

The wildcards themselves are actually characters that have special meanings within SQL WHERE clauses, and SQL supports several wildcard types.

To use wildcards in search clauses, the LIKE operator must be used. LIKE instructs MySQL that the following search pattern is to be compared using a wildcard match rather than a straight equality match.

Predicates?

When is an operator not an operator? When it is a predicate. Technically, LIKE is a predicate, not an operator. The end result is the same; just be aware of this term in case you run across it in the MySQL documentation.

The Percent Sign (%) Wildcard

The most frequently used wildcard is the percent sign (%). Within a search string, % means match any number of occurrences of any character. For example, to find all products that start with the word flo, you can issue the following SELECT statement:

SELECT prod_name
FROM products
WHERE prod_name LIKE 'flo%';

The above statement displayed the following output:

+-----------+
| prod_name |
+-----------+
| flour     |
+-----------+

This example uses a search pattern of 'flo%'. When this clause is evaluated, any value that starts with flo is retrieved. The % tells MySQL to accept any characters after the word flo, regardless of how many characters there are.

Wildcards can be used anywhere within the search pattern, and multiple wildcards can be used as well. The following example uses two wildcards, one at either end of the pattern:

SELECT prod_name
FROM products
WHERE prod_name LIKE '%lou%';

The above statement displayed the following output:

+-----------+
| prod_name |
+-----------+
| flour     |
+-----------+

The search pattern '%lou%' means match any value that contains the text lou anywhere within it, regardless of any characters before or after that text.

Wildcards can also be used in the middle of a search pattern, although that is rarely useful. The following example finds all products that begin with an s and end with an e:

SELECT prod_name
FROM products
WHERE prod_name LIKE 's%e';

It is important to note that, in addition to matching one or more characters, % also matches zero characters. % represents zero, one, or more characters at the specified location in the search pattern.

Watch for Trailing Spaces: Trailing spaces can interfere with wildcard matching. For example, if any of the flour had been saved with one or more spaces after the word flour, the clause WHERE prod_name LIKE '%flour' would not have matched them as there would have been additional characters after the final r. One simple solution to this problem is to always append a final % to the search pattern.

Watch for NULL: Although it might seem that the % wildcard matches anything, there is one exceptionNULL. Not even the clause WHERE prod_name LIKE '%' will match a row with the value NULL as the product name.