Like, You Know, Man... - MySQL

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.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus