Take a look at this example:
SELECT prod_name FROM products WHERE prod_name LIKE '_ product';
The above statement displayed the following output:
+-----------+ | prod_name | +-----------+ | 1 product | | 2 product | +-----------+
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 10 product 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:
SELECT prod_name FROM products WHERE prod_name LIKE '% product';
The above statement displayed the following output:
+------------+ | prod_name | +------------+ | 1 product | | 2 product | | 3 product | | 4 product | | 5 product | | 6 product | | 7 product | | 8 product | | 9 product | | 10 product | | 11 product | +------------+
Unlike %, which can match zero characters, _ always matches one character no more and no less.
Tips for Using Wildcards
As you can see, MySQL's wildcards are extremely powerful. But that power comes with a price: Wildcard searches typically take far longer to process than any other search types discussed previously. Here are some tips to keep in mind when using wildcards:
-
Don't overuse wildcards. If another search operator will do, use it instead.
-
When you do use wildcards, try to not use them at the beginning of the search pattern unless absolutely necessary. Search patterns that begin with wildcards are the slowest to process.
-
Pay careful attention to the placement of the wildcard symbols. If they are misplaced, you might not return the data you intended.
Having said that, wildcards are an important and useful search tool, and one that you will use frequently.