Basic SQL - Learning MySQL - page 145

“ORDER BY Clauses.” Of course, we haven't stored the surname and the given names
separately, and MySQL isn't smart enough to know that Kylie Minogue is a person's
name that should ordinarily be sorted by surname (in phonebook order).
Another very common task you'll want to perform with strings is to find matches that
begin with a prefix, contain a string, or end in a suffix. For example, you might want
to find all album names beginning with the word “Retro.” You can do this with the
LIKE operator in a WHERE clause:
mysql> SELECT album_name FROM album WHERE album_name LIKE "Retro%";
+------------------------------------------+
| album_name |
+------------------------------------------+
| Retro - John McCready FAN |
| Retro - Miranda Sawyer POP |
| Retro - New Order / Bobby Gillespie LIVE |
+------------------------------------------+
3 rows in set (0.00 sec)
Let's discuss in detail how this works.
The LIKE clause is used only with strings and means that a match must meet the pattern
in the string that follows. In our example, we've used LIKE "Retro%" , which means the
string Retro followed by zero or more characters. Most strings used with LIKE contain
the percentage character ( % ) as a wildcard character that matches all possible strings.
You can also use it to define a string that ends in a suffix—such as "%ing" —or a string
that contains a particular substring, such as %Corruption% .
For example, "John%" would match all strings starting with "John" , such as John
Smith and John Paul Getty . The pattern "%Paul" matches all strings that have "Paul" at
the end. Finally, the pattern "%Paul%" matches all strings that have "Paul" in them,
including at the start or at the end.
If you want to match exactly one wildcard character in a LIKE clause, you use the
underscore character ( _ ). For example, if you want all tracks that begin with a three-
letter word that starts with 'R' , you use:
mysql> SELECT * FROM track WHERE track_name LIKE "R__ %";
+----------+----------------+-----------+----------+----------+
| track_id | track_name | artist_id | album_id | time |
+----------+----------------+-----------+----------+----------+
| 4 | Red Right Hand | 2 | 1 | 00:06:11 |
| 14 | Run Wild | 1 | 1 | 00:03:57 |
| 1 | Rip This Joint | 4 | 1 | 00:02:23 |
+----------+----------------+-----------+----------+----------+
3 rows in set (0.00 sec)
The specification "R__ %" means a three-letter word beginning with 'R' —for example
"Red" , "Run" and "Rip" —followed by a space character, and then any string.