Regular Expressions

Use the REGEXP keyword followed by the regular expression (in single quotes) against a query

 

Similar to Like %

 

The usual regular expression operators can be applied:

^ Beginning of string
$ End of string
. Any single character
[...] Any character listed between the square brackets
[^...] Any character not listed between the square brackets
p1|p2|p3 Alternation; matches any of the patterns p1, p2, or p3
* Zero or more instances of preceding element
+ One or more instances of preceding element
{n} n instances of preceding element
{m,n} m through n instances of preceding element

 

Consider the table:

mysql> select * from Students;
+----+-------+---------+------+---------+------+----------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study    | Score |
+----+-------+---------+------+---------+------+----------+-------+
|  1 | Pam   | Frost   | NULL | London  | 1065 | MySQL    |    98 |
|  2 | James | Cross   | 20   | Watford | NULL | Music    |    88 |
|  3 | Andy  | Jones   | 34   | Croydon | 8198 | Markets  |    64 |
|  4 | David | Smith   | 42   | London  | NULL | Antiques |    82 |
|  5 | Julia | James   | NULL | London  | 2108 | Maths    |    77 |
|  6 | Phil  | Watson  | 36   | Hook    | 9437 | Cars     |    44 |
|  7 | Terry | Waite   | 58   | Manc    | 2643 | Music    |    59 |
|  8 | Alex  | Rose    | 49   | Cheam   | NULL | C        |    70 |
+----+-------+---------+------+---------+------+----------+-------+
8 rows in set (0.00 sec)

 

 

Use where Study REGEXP '^M' in the query to obtain all records that start with M in the Study field:

mysql> select * from Students where Study REGEXP '^M';
+----+-------+---------+------+---------+------+---------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study   | Score |
+----+-------+---------+------+---------+------+---------+-------+
|  1 | Pam   | Frost   | NULL | London  | 1065 | MySQL   |    98 |
|  2 | James | Cross   |   20 | Watford | NULL | Music   |    88 |
|  3 | Andy  | Jones   |   34 | Croydon | 8198 | Markets |    64 |
|  5 | Julia | James   | NULL | London  | 2108 | Maths   |    77 |
|  7 | Terry | Waite   |   58 | Manc    | 2643 | Music   |    59 |
+----+-------+---------+------+---------+------+---------+-------+
5 rows in set (0.00 sec)

 

 

Use where Town REGEXP 'don' in the query to obtain all records that contain 'don' in the Town field:

mysql> select * from Students where Town REGEXP 'don';
+----+-------+---------+------+---------+------+----------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study    | Score |
+----+-------+---------+------+---------+------+----------+-------+
|  1 | Pam   | Frost   | NULL | London  | 1065 | MySQL    |    98 |
|  3 | Andy  | Jones   | 34   | Croydon | 8198 | Markets  |    64 |
|  4 | David | Smith   | 42   | London  | NULL | Antiques |    82 |
|  5 | Julia | James   | NULL | London  | 2108 | Maths    |    77 |
+----+-------+---------+------+---------+------+----------+-------+
4 rows in set (0.00 sec)

Leave a Reply