Regular expressions are special strings (character sets) used to match text.
Three tables used: customers, products, and vendors

1. Basic character matching: the keyword REGEXP is followed by a regular expression

#Retrieve all rows of column prod'u name containing text '1000'
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '1000'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.00 sec)

#Match any character with a special character '.'
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '.000'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

The difference between the keyword LIKE and REGEXP:
LIKE matches the whole column. If the matched text appears in the column value, LIKE will not find it. REGEXP matches in the column value. If the matched text appears in the column value, REGEXP will find it and the corresponding row will be returned.

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name LIKE '1000'
    -> ORDER BY prod_name;
Empty set (0.00 sec)#SQL query returned no results

Regular expression matching in MySQL is not case sensitive. But for case sensitivity, you can use the keyword BINARY:

WHERE prod_name REGEXP BINARY 'JetPack .000'

1. For OR matching: use special character '|'

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '1000|2000'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
#More than two OR conditions can also be given
'1000|2000|3000'

1. Match one of several characters: use a set of brackets [], which is another form of OR statement, but there are differences.

#[123] defines a set of characters that means match 1 or 2 or 3, so both 1 ton and 2 ton match and return.
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '[123] ton'#Equivalent to '[1|2|3] ton'
    -> ORDER BY prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

#If [, MySQL will be considered as' 1 'or' 2 'or' 3 ton '.
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '1|2|3 ton'
    -> ORDER BY prod_name;
+---------------+
| prod_name     |
+---------------+
| 1 ton anvil   |
| 2 ton anvil   |
| JetPack 1000  |
| JetPack 2000  |
| TNT (1 stick) |
+---------------+
5 rows in set (0.00 sec)

1. Matching range: a set can be used to define one or more characters to match. Placing a ^ at the beginning of a set can negate a character set. You can use - to define a range, such as [0-9], [a-z].

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '[1-5] ton'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.00 sec)

1. Match special characters: use \ \, such as \ \ (or -, |, [])
   \ is also used to refer to metacharacters:

#Do not use lead\
mysql> SELECT vend_name
    -> FROM vendors
    -> WHERE vend_name REGEXP '.'
    -> ORDER BY vend_name;
+----------------+
| vend_name      |
+----------------+
| ACME           |
| Anvils R Us    |
| Furball Inc.   |
| Jet Set        |
| Jouets Et Ours |
| LT Supplies    |
+----------------+
6 rows in set (0.00 sec)

#Use lead\
mysql> SELECT vend_name
    -> FROM vendors
    -> WHERE vend_name REGEXP '\\.'
    -> ORDER BY vend_name;
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.00 sec)

6. Matching character class

1. Match multiple instances: use repeating metacharacters to have more control over the number of matches to be made.

#\(match (, [0-9] matches any number, sticks? Matches sticks and sticks, \ \))
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
    -> ORDER BY prod_name;
+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
2 rows in set (0.00 sec)

#{4} The character before it is required to appear 4 times exactly
#Equivalent to '[0-9] [0-9] [0-9] [0-9]'
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '[[:digit:]]{4}'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

1. Locators: all of the above examples match text anywhere in a string. To match text in a specific location, you need to use locators.

#To find all products starting with a number or decimal point, you can also use '[0-9] \ \.]'
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '^[[:digit:]\\.]'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.00 sec)

By starting each expression with ^ and ending each expression with $, REGEXP works just LIKE like LIKE.