Overview of MySQL Regular Expressions
MySQL Regular Expressions - Part 1
Division 8
Forward: In this part of the series, I introduce you to the topic, MySQL Regular Expressions.
By: Chrysanthus Date Published: 8 Aug 2012
Introduction
Prerequisite
Before reaching here, you need to have covered the series, whose first parts have the following titles:
- Database
- Normalization
- Referential Integrity in Database
- Implementing Database in MySQL
To reach any of the series, just type the title and my name Chrys in the Search Box of this page and click Search.
The SQL SELECT Statement
Consider the following database table:
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1999-05-13 | NULL |
| Bowser | Diane | dog | m | 1999-08-31 | 2005-07-29 |
| Fluffy | Harold Taylor | cat | f | 2003-02-04 | NULL |
| Claws | Gwen Jones | cat | m | 2004-03-17 | NULL |
| Whistler | Gwen | bird | NULL | 2007-12-09 | NULL |
Let the name of the table be, Pets. The first column has the name of the pet and the second has the name of the owner of the pet. This table has data about pets and the people who own them. The following SELECT statement would select the first and second rows:
SELECT * from Pets where species = "dog";
You know how to select rows, when the string, e.g. “dog” above, matches the value of a column cell in the table. In the above select statement, "dog" matches the first and second cell values in the third column of the table. You know how to do this kind of thing, already. What about the case when you want the rows, where the name (cell value) of the species has a ‘d’ ? In the table, the species names with a ‘d’ are “dog” and “bird”. The first letter in “dog” is ‘d’ and the last letter in “bird” is also ‘d’. In other words, you want the first, second and last rows. The following select statement will select these 3 rows, matching only the character, ‘d’ in the third column.
SELECT * from Pets where species rLike ".*d.*";
In this statement, ".*d.*" , without the quotes, is an example of what is called, a pattern. It means, any string that has the letter, ‘d’. rLike means, like. So the statement selects rows where species are like ".*d.*".
Pattern
A pattern is a set of characters such as ".*d.*" , that determines what is matched in a string. In the above table and in the third column, “dog” and “bird” are matched. “dog” is selected from the two instances of “dog” and “bird” is selected from the single instance of “bird”. There are five choices to choose from, in the third column. The choices are: “dog” (twice), “cat” (twice) and “bird” (once). Each of these choices is a string. Such a string is called, a subject. Note: It is possible for you to have a table of only one row with just one subject. In the above case there are 5 subjects. So in a regular expression situation, you have the subject, the reserved word, rLike, and the pattern. Note: rLike is an operator similar to the assignment operator, = .
The pattern is made up of ordinary characters and Metacharacters. These characters are combined in a special way to form an expression. So you can call a pattern, a regular expression, or simply, regex.
You can have a subject made up of more than one word, like in a sentence. Matching one word in such a subject, is simple. In that case, the regex (pattern) is just the word to match (look for) like in the following statement:
SELECT * from Pets where owner rLike "Gwen";
Here, the regex is, "Gwen", the single word. In the above table, there are two subjects, which are, “Gwen Jones” and “Gwen” from the last two cells of the second column of the table. In this case, “Gwen” will be identified in the last two cells of the second column, and so the select statement will return the last two rows of the table.
NOT rLike
Now, NOT rLike , is the opposite operator to, rLike. Consider the following SELECT statement:
SELECT * from Pets where species NOT rLike ".*d.*";
On the above table, this SELECT statement will return the third and fourth rows that do not have ‘d’ in any of the strings (cell values) in the third column.
Note: In the operator, rLike, the r stands for regex.
Case Sensitivity for Matching
Matching in MySQL Regular Expressions is case insensitive. So the pattern, ".*d.*" and ".*D.*" have the same effect.
Matching Spaces
The space character can also be matched. You type the space in the pattern as usual, using the spacebar key on the keyboard. The following SELECT statement returns the fourth row:
SELECT * from Pets where owner rLike "Gwen ";
The pattern is, "Gwen " . It has a space after “Gwen”. In the table, the only place where “Gwen” followed by a space can be identified, is in the fourth cell in the second column. So the subject matched is, “Gwen Jones” and the fourth row is returned.
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT