MySQL-Regular Expression Detailed Explanation

Similar to the usage of the LIKE operator, REGEXP tells MySQL that what follows is a regular expression.

Basic character matching

In regular expressions, the "." symbol means to match any character, for example:

 SELECT p_name FROM products WHERE p_name REGEXP '.00'

This sentence can match p_names such as 500 and 300

or match

Search for one of two strings, SELECT-like OR statement

 SELECT p_name FROM products WHERE p_name REGEXP '300|500'

matches one of several characters

For example, to match 1bill and 2bill and 3bill, write:

 SELECT p_name FROM products WHERE p_name REGEXP '[1|2|3]bill'

It can also be directly abbreviated as:

 SELECT p_name FROM products WHERE p_name REGEXP '[123]bill'

Negation of matching charset sum

Just add a ^ in front, for example to find any character except 123:

 SELECT p_name FROM products WHERE p_name REGEXP '[^123]'

match range

You can use '-' combined with [] to define the range, such as [1-3], [cg] For example:

 SELECT p_name FROM products WHERE p_name REGEXP '[1-3]bill'

match special characters

Matching special characters needs to start with '//', such as matching '.':

 SELECT p_name FROM products WHERE p_name REGEXP '//.'

match character class

📢 Not very common:

match multiple instances

For example, if you want to match both 'Bill (1 apple)' and 'Bill (8 apples)':

 SELECT p_name FROM products WHERE p_name REGECXP 'Bill \\([0-9] apple?\\)'

The key lies in the processing of apples and apples, here is the '?', in addition to the following:

match a specific location

The above are all matching at any position. The following describes the matching at the beginning or end of text and words:
For example, to match '1 apple'8 apple':

 SELECT p_name FROM products WHERE p_name REGECXP '^[0-9] apple'

In addition to the text beginning with '^' and ending with '$', there are these:

---

Reference: Forta B. MySQL crash course[M]. Pearson Education India, 2006.