MySQL Pattern Matching

MySQL provides standard SQL pattern matching and extended regular expressions.

SQL Pattern matching

In SQL Pattern matching, _ is used to match single character and % is used to match an arbitrary number of characters.

Starting with character -The given query returns all records of students whose name starting with 'a'.

mysql->SELECT * FROM students where name 'a%';

Containing match character - The given query returns all records of students whose name contain 'z' character.

mysql->SELECT * FROM students where name '%z%';

Ending with character - The given query returns all records of students whose name end with 'ty'.

mysql->SELECT * FROM students where lastname '%ty';

To apply extended regular expression REGEXP and NOT REGEXP operators are used with pattern matching characters.

Pattern	Description
.	To match any single character
^	To match beginning of string
$	To match end of string
[...]	To match any character between square bracket. Ex- [pqr] matches p, q, r, [a-z] matches any letter, [0-9] matches any digit.
*	To match zero or more instances of the preceding thing
+	To match one or more instances of the preceding thing

Examples

To get name of students starting with 'a'

mysql->SELECT * FROM students where name REGEXP '^a';

To get name of students containing a 'z'

mysql->SELECT * FROM students where name REGEXP 'z';

To get name of students ending with 'fy'

mysql->SELECT * FROM students where lastname REGEXP 'fy$';

To get name of students containing exactly four characters.

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