MySQL Regular Expression Exercise

Write a mysql statement to get name of students containing exactly four characters.

Suppose the student table is -

 
+----+-------+---------+------------+
| id | name  | dept_id | birth      |
+----+-------+---------+------------+
|  1 | Maria | 2       | 1994-03-12 |
|  2 | John  | 1       | 1993-02-07 |
|  3 | Gal   | 4       | 1992-09-11 |
|  4 | Jakey | 2       | 1990-08-31 |
|  5 | Rama  | 1       | 1994-12-09 |
|  6 | Maria | 4       | 1993-10-09 |
+----+-------+---------+------------+

Solution

To apply extended regular expression REGEXP operator is used with pattern matching characters. The caret (^) is used to start the match at the beginning of string. The [...] matches any character between the square bracket. The caret ($) is used to start the match at the end of string.

The following statement retrieves the name of students containing exactly four characters.

mysql> SELECT * FROM student where name REGEXP '^....$';

Output of the above statement -

+----+------+---------+------------+
| id | name | dept_id | birth      |
+----+------+---------+------------+
|  2 | John | 1       | 1993-02-07 |
|  5 | Rama | 1       | 1994-12-09 |
+----+------+---------+------------+