View source for MySQL Regular Expression Searches

In previous chapters we have looked at some basic data retrieval filtering techniques using comparison operators and wildcards. Whilst this approaches to data filtering are fine for basic searching, more advanced searching quickly becomes cumbersome using these techniques.

In this chapter we will look at yet another filtering and searching technique supported by MySQL called ''regular expressions''.

== What are Regular Expressions? ==

The term ''regular expressions'' is one of those technical terms where the words do very little to explain what the technology does. regular expressions are a feature common to many programming languages and are a topic on which entire can, and indeed, have been written. Fortunately (or unfortunately depending on your perspective), MySQL does not provide as extensive support for regualr expressions as some other programming languages. This is good in that it makes the subject area easier to learn, but may be frustrating if you are already proficient with the rich regular expression syntax available in other languages.

Regular expressions are essentially a set of instructions using a predefined syntax for matching text in a variety of flexible ways. For example, you might want to extract all the occurrences of a particular word sequence from a block of text. You might also want to perform a search for a particular piece of text and replace it with some alternate text.

== Regular Expression Character Matching ==

In order to introduce the ''REGEXP'' operator, we will begin by looking at a use of regular expressions that could similarly be used with the ''LIKE'' operator. As in the previous chapter we need to retrieve rows from a table taking into consideration the difference in spelling of the color gray (grey). To perform this retrieval we will use the regex dot character matching (.). Rather like the ''LIKE'' underscore wildcard, this character indicates that any character in this position in the text will be considered a match. For example:

<pre>
mysql> SELECT * FROM product WHERE prod_name REGEXP 'Gr.y Computer Case';
+-----------+--------------------+-------------------+
| prod_code | prod_name          | prod_desc         |
+-----------+--------------------+-------------------+
|        11 | Grey Computer Case | ATX PC CASE       |
|        12 | Gray Computer Case | ATX PC CASE (USA) |
+-----------+--------------------+-------------------+
2 rows in set (0.05 sec)
</pre>

So far we haven't done anything that could not have been achieved using wildcards. Regular expressions, however, do not stop here.

== Matching from a Group of Characters ==

One problem with the approach outlined above is that any letter between the 'Gr' and the 'y' would have registered as a match. In reality we are only interested in words that contain either an 'a' or an 'e' in that location. Fortunately, regular expressions allow us to specify a group of acceptable character matches for any character position. The syntax for this requires that the characters be places in square brackets at the desired location in the match text. For example:

<pre>
mysql> SELECT * FROM product WHERE prod_name REGEXP 'Gr[ae]y Computer Case';
+-----------+--------------------+-------------------+
| prod_code | prod_name          | prod_desc         |
+-----------+--------------------+-------------------+
|        11 | Grey Computer Case | ATX PC CASE       |
|        12 | Gray Computer Case | ATX PC CASE (USA) |
+-----------+--------------------+-------------------+
2 rows in set (0.00 sec)
</pre>

Use of this syntax ensures that only the words 'Grey' and 'Gray'' will match the search criteria. There is no limit to the number of characters that can be grouped in the brackets when using this filtering technique.

== Matching from a Range of Characters ==

The character group matching syntax can be extended to cover range of characters. For example, instead of declaring a regular expression to cover the letters between A and F as [ABCDEF] we could simply specify a range of characters using the '-' character between the upper and lower ranges [A-F]. We could, for example, list a product based on certian model numbers which begin with numbers ranging from 1 to 6:

<pre>
mysql> SELECT * FROM product WHERE prod_name REGEXP 'CD-RW Model [1-6]543';
+-----------+------------------+-----------+
| prod_code | prod_name        | prod_desc |
+-----------+------------------+-----------+
|         1 | CD-RW Model 4543 | CD Writer |
|        14 | CD-RW Model 5543 | CD Writer |
|        15 | CD-RW Model 6543 | CD Writer |
|        16 | CD-RW Model 2543 | CD Writer |
+-----------+------------------+-----------+
4 rows in set (0.00 sec)
</pre>

== Handling Special Characters ==

As you have seen, regular expressions assign special meaning to particular characters. For example the dot (.) and square brackets ([]) all have special meaning. Those who studying critical thinking at college will already be questioning what to do if the character sequence that is the subject of a search contains one or more of these characters. Obviously if you are are looking for the following text that looks like a regular expression, the text for which you want to search is, itself, going to be viewed as regular expression syntax.

To address this issue, a concept known as ''escaping'' is used. In SQL, escaping involves preceding any characters that may be mis-interpreted as a regular expression special character with double back slashes (\\). For example, suppose we have a row in our product table which reads as follows:

<pre>
+-----------+--------------------+-----------+
| prod_code | prod_name          | prod_desc |
+-----------+--------------------+-----------+
|        17 | CD-RW Model [7543] | CD Writer |
+-----------+--------------------+-----------+
</pre>

If we were to search for this without regard to the fact that the prod_name value contains regular expression special characters we will not get what we wanted:

<pre>
mysql> SELECT * FROM product WHERE prod_name REGEXP 'CD-RW Model [7543]';
+-----------+------------------+-----------+
| prod_code | prod_name        | prod_desc |
+-----------+------------------+-----------+
|         1 | CD-RW Model 4543 | CD Writer |
|        14 | CD-RW Model 5543 | CD Writer |
+-----------+------------------+-----------+
2 rows in set (0.00 sec)
</pre>

The cause of the problem is that the regular expression has been interpreted as a search for any entries that read 'CD-RW Model' followed by either a 7, 5, 4 or 3. Clearly, what we wanted was the actual text [7543]. If instead we escape the brackets with the \\ escape sequence we get the result we want:

<pre>
mysql> SELECT * FROM product WHERE prod_name REGEXP 'CD-RW Model \\[7543\\]';
+-----------+--------------------+-----------+
| prod_code | prod_name          | prod_desc |
+-----------+--------------------+-----------+
|        17 | CD-RW Model [7543] | CD Writer |
+-----------+--------------------+-----------+
1 row in set (0.00 sec)
</pre>

== Regular Expressions and Whitespace Characters ==

Regular expression syntax also provides a mechanism to reference whitespace characters such as tabs, carriage returns and line feeds. These are referenced in a regular expression using ''metacharacters''. These metacharacters are outlined in the following table:

<table border="1" cellspacing="0">
<tr style="background:#efefef;">
<th>Metacharacter<th>Description</th>
<tr>
<td>\\n<td>New line (line feed)</td>
<tr>
<td>\\f<td>Form feed</td>
<tr>
<td>\\t<td>Tab</td>
<tr>
<td>\\r<td>Carriage return</td>
<tr>
<td>\\v<td>Vertical tab</td>
</table>

== Location Specific Matches ==



== Matching by Character Type ==

Another useful regular expression trick is to match characters by type or ''class''. For example we might need to specify that a character must be a letter, a number or a alphanumeric. This involves the use of some special class definitions outlines in the following table. Some examples of these special classes in action follow the table:

<table border="1" cellspacing="0">
<tr style="background:#efefef;">
<th>Class Keyword<th>Description of Matches</th>
<tr>
<td>[:alnum:]<td>Alphanumeric - any number or letter. Equivalent to [a-z], [A-Z] and [0-9]</td>
<tr>
<td>[:alpha:]<td>Alpha - any letter. Equivalent to [a-z] and [A-Z]</td>
<tr>
<td>[:blank:]<td>Space or Tab. Equivalent to [\\t] and [ ]</td>
<tr>
<td>[:cntrl:]<td>ASCII Control Character</td>
<tr>
<td>[:digit:]<td>Numeric. Equivalent to [0-9]</td>
<tr>
<td>[:graph:]<td>Any character with the exception of space</td>
<tr>
<td>[:lower:]<td>Lower case letters. Equivalent to [a-z]</td>
<tr>
<td>[:print:]<td>Any printable character</td>
<tr>
<td>[:punct:]<td>Characters that are neither control characters, nor alphanumeric (i.e punctuation characters)</td>
<tr>
<td>[:space:]<td>Any whitespace character (tab, new line, form feed, space etc)</td>
<tr>
<td>[:upper:]<td>Upper case letters. Equivalent to [A-Z]</td>
<tr>
<td>[:xdigit:]<td>Any hexadecimal digit. Equivalent to [A-F], [a-f] and [0-9]
</table>

Let's now look at some examples. Suppose in our product database we have two products with similar names, the 'One&One VoIP Headset' and the "One2One USB Hub'. In order to retrieve the 'One2One' product we would use the [:digit:] character class:

<pre>
mysql> SELECT * FROM product WHERE prod_name REGEXP 'One[[:digit:]]One';
+-----------+-----------------+----------------+
| prod_code | prod_name       | prod_desc      |
+-----------+-----------------+----------------+
|        19 | One2One USB Hub | 4 Port USB Hub |
+-----------+-----------------+----------------+
1 row in set (0.00 sec)
</pre>

Similarly, to extract the 'One&One' product we would use the [:punct:] class:

<pre>
mysql> SELECT * FROM product WHERE prod_name REGEXP 'One[[:punct:]]One';
+-----------+----------------------+----------------+
| prod_code | prod_name            | prod_desc      |
+-----------+----------------------+----------------+
|        18 | One&One VoIP Headset | Stereo Headset |
+-----------+----------------------+----------------+
1 row in set (0.02 sec)
</pre>

== Regular Expression Repetition Metacharacters ==

In addition to allowing searches on single instances, regular expressions can also be written to look for repetition in text. this is achieved using a set of ''repetition metacharacters'':

<table border="1">
<th>Metacharacter<th>Description</th>
<tr>
<td>*<td>Any number of matches</td>
<tr>
<td>+<td>One or more matches</td>
<tr>
<td>{n}<td>''n'' number of matches</td>
<tr>
<td>{n,}<td>Not less than ''n'' number of matches</td>
<tr>
<td>{n1,n2}<td>A range of matches between n1 and n2</td>
</table>