MySQL Pattern Matching

MySQL provides standard SQL pattern matching and extended regular expressions. In this _ is used to match single character and % is used to match an arbitrary number of characters, like -

To return all records starting with 'a'

SELECT * FROM table_name WHERE field_name 'a%';

To return all records containing 'z' character

SELECT * FROM table_name WHERE field_name '%z%';

To return all records end with 'ly'

SELECT * FROM table_name WHERE field_name '%ly';




MySQL Pattern Matching Exercise

Write a mysql statement to retrieve name beginning with 'm'.

Suppose the table is -

+----+--------------+------------+------------+
| id | name         | department | birth      |
+----+--------------+------------+------------+
|  1 | Maria Gloria | CS         | 1994-03-12 |
|  2 | John Smith   | IT         | 1993-02-07 |
|  3 | Gal Rao      | CS         | 1992-09-11 |
|  4 | Jakey Smith  | EC         | 1990-08-31 |
|  5 | Rama Saho    | IT         | 1994-12-09 |
|  6 | Maria Gaga   | EC         | 1993-10-09 |
+----+--------------+------------+------------+

Solution

To apply extended regular expression, REGEXP operator is used with pattern matching characters.
To find names beginning with 'm', use ^ to match the beginning of the name. It returns 1 if the string expr matches the regular expression specified by the pattern 'm', 0 otherwise.

 mysql> SELECT * FROM students
     -> WHERE name REGEXP '^m';

Output of the above statement -

+----+--------------+------------+------------+
| id | name         | department | birth      |
+----+--------------+------------+------------+
|  1 | Maria Gloria | CS         | 1994-03-12 |
|  6 | Maria Gaga   | EC         | 1993-10-09 |
+----+--------------+------------+------------+




Related MySQL Exercises

MySQL concatenate
MySQL Get Current Date, User, Version
MySQL Get Maximum Value
MySQL Administrator
MySQL where clause
MySQL order by
MySQL get difference between two dates
MySQL Join
MySQL Regular Expression
MySQL delete duplicate row
MySQL update multiple rows
Get nth highest salary using MySQL
Display the nth row from MySQL
Commit and rollback in mysql
MySQL SELECT top 5
MySQL display the alternate rows




Read more articles


General Knowledge



Learn Popular Language