Speaking SQL (part 2) - Like, You Know, Man... (
Page 6 of 8 )
SQL also offers the LIKE keyword, which is used to return results from a
wildcard search and comes in very handy when you're not sure what you're looking
for. There are two types of wildcards allowed in a LIKE construct: the %
character, which is used to signify zero or more occurrences of a character, and
the _ character, which is used to signify exactly one occurrence of a
character.
Let's suppose I wanted a list of all members whose first names
contained the letter "e". My query would look like this:
mysql> SELECT * FROM members WHERE fname LIKE '%e%';
+-----------+-------+---------+---------+----------------------+
| member_id | fname | lname | tel | email |
+-----------+-------+---------+---------+----------------------+
| 2 | Jane | Doe | 8373728 | jane@site.com |
| 3 | Steve | Klingon | 7449373 | steve@alien-race.com |
+-----------+-------+---------+---------+----------------------+
2 rows in set (0.16 sec)
I could use LIKE to generate a list of members whose name
begins with the letter "s"
mysql> SELECT * FROM members WHERE fname LIKE 's%';
+-----------+-------+---------+---------+----------------------------
| member_id | fname | lname | tel | email
+-----------+-------+---------+---------+----------------------------
| 3 | Steve | Klingon | 7449373 | steve@alien-race.com
| 4 | Santa | Claus | 9999999 | santa@the-north-pole.com |
+-----------+-------+---------+---------+----------------------------
2 rows in set (0.00 sec)
or search through my "videos" collection for movies
containing the word segment "man" in their title.
mysql> SELECT title, director FROM videos WHERE title LIKE '%man%';
+--------------+----------------+
| title | director |
+--------------+----------------+
| Hollow Man | Paul Verhoeven |
| Woman On Top | Fina Torres |
+--------------+----------------+
2 rows in set (0.05 sec)
This article copyright Melonfire
2001. All rights reserved.
