You have seen MySQL pattern matching with LIKE ...%. MySQL supports another type of pattern matching operation based on the regular expressions and the REGEXP operator. If you are aware of PHP or PERL, then it is very simple for you to understand because this matching is same like those scripting the regular expressions.
Following is the table of pattern, which can be used along with the REGEXP operator.
| Pattern | What the pattern matches |
|---|---|
| ^ | 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 |
Examples
Now based on above table, you can device various type of SQL queries to meet your requirements. Here, I am listing a few for your understanding.
Consider we have a table called customers and it is having a field called first_name
mysql> select * from customers;
+----+------------+-----------+---------------+-------------------+----------------+--------+
| id | first_name | last_name | mobile_phone | email_address | address | city |
+----+------------+-----------+---------------+-------------------+----------------+--------+
| 1 | Bedecs | Anna | (123)555-0112 | bedecs@gmail.com | 123 1st Street | kanpur |
| 2 | Solsona | Antonio | (123)555-0113 | solsona@gmail.com | 123 2nd Street | mumbai |
| 3 | Axen | Thomas | (123)555-0114 | axen@gmail.com | 123 3rd Street | pune |
+----+------------+-----------+---------------+-------------------+----------------+--------+
3 rows in set (0.00 sec)