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) |