Previous Page
Next Page

Using 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.

New Term

Wildcards Special characters used to match parts of a value.


New Term

Search pattern 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.

Note

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 jet, you can issue the following SELECT statement:

• Input

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';

• Output

+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| JP1000  | JetPack 1000 |
| JP2000  | JetPack 2000 |
+---------+--------------+

• Analysis

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

Note

Case-Sensitivity Depending on how MySQL is configured, searches might be case-sensitive, in which case 'jet%' would not match JetPack 1000.


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:

• Input

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%anvil%';

• Output

+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+

• Analysis

The search pattern '%anvil%' means match any value that contains the text anvil 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:

• Input

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.

Note

Watch for Trailing Spaces Trailing spaces can interfere with wildcard matching. For example, if any of the anvils had been saved with one or more spaces after the word anvil, the clause WHERE prod_name LIKE '%anvil' would not have matched them as there would have been additional characters after the final l. One simple solution to this problem is to always append a final % to the search pattern. A better solution is to trim the spaces using functions, as is discussed in Chapter 11, "Using Data Manipulation Functions."


Caution

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.


The Underscore (_) Wildcard

Another useful wildcard is the underscore (_). The underscore is used just like %, but instead of matching multiple characters, the underscore matches just a single character.

Take a look at this example:

• Input

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

• Output

+---------+-------------+
| prod_id | prod_name   |
+---------+-------------+
| ANV02   | 1 ton anvil |
| ANV03   | 2 ton anvil |
+---------+-------------+

• Analysis

The search pattern used in this WHERE clause specifies a wildcard followed by literal text. The results shown are the only rows that match the search pattern: The underscore matches 1 in the first row and 2 in the second row. The .5 ton anvil product did not match because the search pattern matched a single character, not two. By contrast, the following SELECT statement uses the % wildcard and returns three matching products:

• Input

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '% ton anvil';

• Output

+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+

Unlike %, which can match zero characters, _ always matches one characterno more and no less.


Previous Page
Next Page