Using Regular Expressions allows you to select data that matches a certain format (or perhaps data that does not match a format). For this we use the LIKE and NOT LIKE clauses.
The first thing we are going to do is create a people table that we can use to play around with.
CREATE TABLE IF NOT EXISTS `people` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; INSERT INTO `people` (`id`, `name`, `date`) VALUES (1, 'James', '2009-09-16'), (2, 'Joe', '2009-09-30'), (3, 'Jack', '2009-09-18'), (4, 'Bob', '2009-09-17'), (5, 'Tiik', '2004-09-16');
The table has three fields, id, name, and date. We can use regular expressions to find all customers who have not registered in 2009 for example. We can also use it fo find all names that start with "J" or end with "k".
I use these functions a lot. They are very useful for implementing searches in programs.
LIKE clause
Let us first select all the names of people who start with J.
We use % to indicate to match any string of any length.
So:
'J%' means to match any string that starts with "J" and followed by a string of any length.
Try this:
SELECT name FROM people WHERE name LIKE 'J%'
Return values:
James
Joe
Jack
Now using the same % wildcard, we are going to display all names that end with 'k'.
Instead of putting the % wildcard at the end of the pattern, we are going to put it at the beginning. This means to find all strings that string art with any characters (of any length) and end with k.
Example:
SELECT name FROM people WHERE name LIKE '%k'
Output:
Jack
Tiik
This code lets us find all values that end with characters.
NOT LIKE
We use NOT LIKE to find things that do not match a certain pattern. We are going to find all strings that do not start with a j.
Try out this code:
SELECT name FROM people WHERE name NOT LIKE 'j%'
Output:
Bob
Tiik
These two "functions" are really helpful for implementing searches. I use them a lot in my program when I use searches of database.
Other wildcard available are:
_ to match any character. This can be useful for locating something when you know part of it but you don't know all of it.
Say you want to find a phone number but you only know the first 2 digits, you can write a query like this:
SELECT * FROM contacts WHERE phone_number LIKE '51_-___-____'.
This will return things like:
515-111-1111
512-134-abcd
Anything that is in the format of a phone number.
This is very simple, but is VERY useful. Next, we will look at using REGEXP for even more control.