Note: the key to using regular expressions is REGEXP

1. Query records that start with a specific character or string

The character "^" matches the text at the beginning of a specific character or string

#In the fruits table, query the records whose f ﹣ name field begins with the letter b
MariaDB [vincen]> select * from fruits
    -> where 
    -> f_name REGEXP '^b'
    -> ;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| t1   |  102 | banana     |   10.30 |
+------+------+------------+---------+
3 rows in set (0.01 sec)

2. Query records ending with a specific character or string

Character '$' matches text at the end of a specific character or string

#In the fruits table, query the records whose f ﹐ name field ends with y
MariaDB [vincen]> select * from fruits
    -> where
    -> f_name REGEXP 'y$'
    -> ;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| c0   |  101 | cherry     |    3.20 |
| m2   |  105 | xbabay     |    2.60 |
+------+------+------------+---------+
4 rows in set (0.00 sec)

3. Replace any string in a character

Character "." matches any one character

#In the fruits table, query the records where the value of the f u name field contains the letters a and g and there is only one letter between them
MariaDB [vincen]> select * from fruits
    -> where
    -> f_name REGEXP 'a.g'
    -> ;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs1  |  102 | orange |   11.20 |
| m1   |  106 | mango  |   15.60 |
+------+------+--------+---------+
2 rows in set (0.01 sec)

4. Match multiple characters

"*" matches the preceding characters any number of times, including 0 times

#In the fruits table, query the f ﹣ u name field to start with the letter b, and the record with the letter a appears after b
MariaDB [vincen]> select * from fruits
    -> where
    -> f_name REGEXP '^ba*'
    -> ;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| t1   |  102 | banana     |   10.30 |
+------+------+------------+---------+
3 rows in set (0.01 sec)

"+" matches preceding characters at least once

#Query records in fruits table where f ﹣ u name field starts with b and a appears at least once after b
MariaDB [vincen]> select * from fruits
    -> where
    -> f_name REGEXP '^ba+'
    -> ;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| t1   |  102 | banana |   10.30 |
+------+------+--------+---------+
1 row in set (0.00 sec)

5. Match specified string

If you want to match the specified single character, you can write the character directly in single quotation marks

If you want to match more than one specified character, you need to separate it with a "|" symbol (called a pipe character in linux)

#Query the fruits table for records with the on character in field F ﹣ name
MariaDB [vincen]> select * from fruits
    -> where
    -> f_name REGEXP 'on'
    -> ;
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| bs2  |  105 | melon   |    8.20 |
| l2   |  104 | lemon   |    6.40 |
| o2   |  103 | coconut |    9.20 |
+------+------+---------+---------+
3 rows in set (0.00 sec)

#Query the records with on or ap in field F ﹣ name in fruits table
MariaDB [vincen]> select * from fruits
    -> where
    -> f_name REGEXP 'on|ap'
    -> ;
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| a1   |  101 | apple   |    5.20 |
| a2   |  103 | apricot |    2.20 |
| bs2  |  105 | melon   |    8.20 |
| l2   |  104 | lemon   |    6.40 |
| o2   |  103 | coconut |    9.20 |
| t2   |  102 | grape   |    5.30 |
+------+------+---------+---------+
6 rows in set (0.01 sec)

6. Match any one of the specified characters

Square bracket [] specifies a character set and a value set

#Query the records with the letter o or b in the field F ﹣ name in the fruits table
MariaDB [vincen]> select * from fruits
    -> where
    -> f_name REGEXP '[ob]'
    -> ;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.60 |
| m2   |  105 | xbabay     |    2.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+
11 rows in set (0.01 sec)

#Query the records with 3 or 4 or 5 or 6 in the s UU ID field in the fruits table
MariaDB [vincen]> select * from fruits
    -> where
    -> s_id REGEXP '[3456]'
    -> ;
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| a2   |  103 | apricot |    2.20 |
| b2   |  104 | berry   |    7.60 |
| bs2  |  105 | melon   |    8.20 |
| l2   |  104 | lemon   |    6.40 |
| m1   |  106 | mango   |   15.60 |
| m2   |  105 | xbabay  |    2.60 |
| m3   |  105 | xxtt    |   11.60 |
| o2   |  103 | coconut |    9.20 |
+------+------+---------+---------+
8 rows in set (0.01 sec)

7. Match characters other than specified

[^ character set] matches any character not in the specified character set

#In the fruits table, query the f ﹣ ID field to contain character records other than letters a~e and numbers 1 ~ 2
MariaDB [vincen]> select * from fruits
    -> where
    -> f_id REGEXP '[^a-e 1-2]'
    -> ;
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| b5   |  107 | xxxx    |    3.60 |
| bs1  |  102 | orange  |   11.20 |
| bs2  |  105 | melon   |    8.20 |
| c0   |  101 | cherry  |    3.20 |
| l2   |  104 | lemon   |    6.40 |
| m1   |  106 | mango   |   15.60 |
| m2   |  105 | xbabay  |    2.60 |
| m3   |  105 | xxtt    |   11.60 |
| o2   |  103 | coconut |    9.20 |
| t1   |  102 | banana  |   10.30 |
| t2   |  102 | grape   |    5.30 |
| t4   |  107 | xbababa |    3.60 |
+------+------+---------+---------+
12 rows in set (0.01 sec)

8. Specify the number of character occurrences

"Character {n,}" means to match at least n characters before it

#Query the records with the letter x in the f ﹣ name field in the fruits table at least twice
MariaDB [vincen]> select * from fruits
    -> where
    -> f_name REGEXP 'x{2,}'
    -> ;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b5   |  107 | xxxx   |    3.60 |
| m3   |  105 | xxtt   |   11.60 |
+------+------+--------+---------+
2 rows in set (0.01 sec)

"Character {n,m}" means that the string before the match is not less than n times, not more than m times. (n < = Times < = m)

#In the fruits table, query the records where the string ba appears at least once and at most three times in the f_name field
MariaDB [vincen]> select * from fruits
    -> where
    -> f_name REGEXP 'ba{1,3}'
    -> ;
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| m2   |  105 | xbabay  |    2.60 |
| t1   |  102 | banana  |   10.30 |
| t4   |  107 | xbababa |    3.60 |
+------+------+---------+---------+
3 rows in set (0.00 sec)