34,333
edits
Changes
→Regular Expression Repetition Metacharacters
<tr>
<td>{n1,n2}<td>A range of matches between n1 and n2</td>
<tr>
<td>?<td>Optional single character match (character my be present or not to qualify for a match</td>
</table>
As always, example do a much better job of demonstrating a concept than data in a table. Let's begin by searching for all 4 digit number sequences in our prod_name column:
<pre>
mysql> SELECT * FROM product WHERE prod_name REGEXP '[[:digit:]]{3}';
+-----------+---------------------+-----------------+
| prod_code | prod_name | prod_desc |
+-----------+---------------------+-----------------+
| 1 | CD-RW Model 4543 | CD Writer |
| 2 | EasyTech Mouse 7632 | Cordless Mouse |
| 3 | WildTech 250Gb 1700 | SATA Disk Drive |
| 7 | Dell XPS 400 | Desktop PC |
| 14 | CD-RW Model 5543 | CD Writer |
| 15 | CD-RW Model 6543 | CD Writer |
| 16 | CD-RW Model 2543 | CD Writer |
| 17 | CD-RW Model [7543] | CD Writer |
+-----------+---------------------+-----------------+
8 rows in set (0.00 sec)
</pre>
In the above example we have indicated that we are looking for any sequence of 3 digits by using the ''[[:digit:]]{3}'' regular expression. In this case we have picked up entries with both 3 and 4 digits in a sequence. Suppose, instead we wanted only 4 digit sequences:
<pre>
mysql> SELECT * FROM product WHERE prod_name REGEXP '[[:digit:]]{4,}';
+-----------+---------------------+-----------------+
| prod_code | prod_name | prod_desc |
+-----------+---------------------+-----------------+
| 1 | CD-RW Model 4543 | CD Writer |
| 2 | EasyTech Mouse 7632 | Cordless Mouse |
| 3 | WildTech 250Gb 1700 | SATA Disk Drive |
| 14 | CD-RW Model 5543 | CD Writer |
| 15 | CD-RW Model 6543 | CD Writer |
| 16 | CD-RW Model 2543 | CD Writer |
| 17 | CD-RW Model [7543] | CD Writer |
+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)
</pre>
Here we see that our Dell XPS 400 is no longer listed because it has only 3 digits.
The '?' metacharacter is particularly useful when we need to allow for plural words. For example, we may want to list any product descriptions where the word Drive or Drives is used. To achieve this we simply follow the 's' with a '?', thereby making the trailing 's' optional for a match:
<pre>
mysql> SELECT * FROM product WHERE prod_desc REGEXP 'Drives?';
+-----------+------------------------+--------------------+
| prod_code | prod_name | prod_desc |
+-----------+------------------------+--------------------+
| 3 | WildTech 250Gb 1700 | SATA Disk Drive |
| 20 | MasterSlave Multi-pack | 5 SATA Disk Drives |
+-----------+------------------------+--------------------+
2 rows in set (0.00 sec)
