Table of Contents for

MySQL in a Nutshell, 2nd Edition

Once our database is loaded with large amounts of data, it can be cumbersome to locate data simply by scrolling through the results of SELECT statements. Also, sometimes we don’t have the exact or complete text for a column we’re examining. For these situations, we can use the LIKE operator. Suppose that our books table now has thousands of entries. Suppose further that a customer says he’s looking for a specific book. He can’t remember the name of the author or the title of the book, but he does remember that the words traveler and winter are in the title. We could enter this SQL statement to search the database based on this minimal information:

SELECT book_id, title,
CONCAT(author_first, ' ', author_last) AS author
FROM books
JOIN authors USING(author_id)
WHERE title LIKE '%traveler%'
AND title LIKE '%winter%';
   
+---------+-----------------------------------+---------------+
| book_id | title                             | author        |
+---------+-----------------------------------+---------------+
|    1400 | If on a winter's night a traveler | Italo Calvino |
+---------+-----------------------------------+---------------+

With the LIKE operator, we use the percent sign wildcard twice to indicate that we’re searching for all rows in which the title column’s data contains the string traveler with zero or more characters before it (the preceding percent sign), and zero or more characters after it (the terminating percent sign). Put another way, the word traveler must be contained somewhere in the column’s data to have a pattern match. The next part of the clause indicates that winter must also be found in the same column. Incidentally, the LIKE keyword is an operator like the equals sign.

If another customer asks us to search the database for a Graham Greene book with either the word Stamboul or the word Orient in the title, we could use OR within an expression like this:

SELECT book_id, title
FROM books
WHERE author_id = 1
AND title LIKE '%Stamboul%' 
OR author_id = 1
AND title LIKE '%Orient%';

Since we already know the author’s identification number, this statement is more succinct and includes only one table. Notice that we have to specify the author_id in each expression; otherwise we might get results by other authors that match the words for which we’re searching. For more information on operators, see Appendix B. You can find more examples and possibilities for searching data in Chapter 6.