How does MySQL use regular expressions? (code example)

MySQL supports another pattern matching operation based on regular expressions and the regexp operator. The following article will introduce you to the regular expressions (Regexp) of MySQL and briefly introduce how to use regular expressions (Regexp) in MySQL through examples. I hope it will be helpful to you.

A brief introduction to MySQL regular expressions (Regexp)

1. It provides It provides powerful and flexible matching patterns that can help us implement powerful search utilities for database systems.

2. regexp is an operator used when performing regular expression pattern matching, and rlike is a synonym.

3. It also supports a number of metacharacters that provide greater flexibility and control when performing pattern matching.

4. Backslash is used as an escape character. If double backslashes are used, they are only considered in pattern matching.

5. It is not case sensitive.

The following is a table of regular patterns that can be used with the REGEXP operator. 【Recommended related video tutorials: MySQL video tutorial】

##[[:<:]] Matches the beginning of a word. [[:>:]] Matches the end of a word. [:class:] Matches character classes, that is, [:alpha:] matches letters, [:space:] matches spaces, and [:punct:] matches Punctuation, [:upper:] matches upper letters.##{n} Match n sub-expressions that precede n elements {m,n} Match m to n sub-expressions that precede the elements

Pattern	Description (specifically what is matched)
^	Match the beginning of the string.
$	Matches the end position of the string.
.	Matches any single character except "\n".
[...]	matches any character contained in it.
[^...]	Matches any character not included.
?	Matches zero or one subexpression (string) preceding it.
*	Matches zero or more subexpressions (strings) preceding it.
	Matches one or more subexpressions (strings) preceding it.
[abc]	Matches any characters listed between square brackets.
[^abc]	Matches any characters not listed between square brackets.
[A-Z]	matches any uppercase letter.
[a-z]	matches any lowercase letter.
[0-9]	Matches any number from 0 to 9.
p1|p2|p3	Matches any pattern, p1 or p2 or p3

Usage example:

The following uses a simple example to introduce the use of MySQL regular expressions (Regexp).

1. Match the beginning of the string (^):

Match all data whose name field starts with 'sa', for example: Sample-sam, samarth.

SELECT name FROM student_tbl WHERE name REGEXP '^sa';

2. Match the end of the string ($):

Match all data whose name field ends with 'on', for example: norton, merton.

SELECT name FROM student_tbl WHERE name REGEXP 'on$';

3. Match zero or one subexpression (?) of the string preceding it:

Match all data whose title field contains 'com', for example : Comedy, romantic comedy.

SELECT title FROM movies_tbl WHERE title REGEXP 'com?';

4. Match any pattern p1 or p2 or p3 (p1 | p2 | p3):

Match all names whose name field contains 'be' or 'ae' Data, for example: Abel, Baer.

SELECT name FROM student_tbl WHERE REGEXP 'be|ae' ;

5. Match any characters listed between square brackets ([abc]):

Match all names whose name field contains 'j' or 'z' Data, for example: Lorentz, Rajs.

SELECT name FROM student_tbl WHERE REGEXP '[jz]' ;

6. Match any single lowercase letter ([a-z]) ([a-z] and (.)) between 'a' and 'z':

Match all string data in which the first digit in the name field contains letters in the range of "b" and "g", the second digit contains any character, and the third digit contains the letter 'a', for example: Tobias, sewall.

Match any single character (.)

SELECT name FROM student_tbl WHERE REGEXP '[b-g].[a]' ;

7. Match any character not listed between square brackets ([^ abc]):

Matches all names that do not contain 'j' or 'z'. Example: nerton, sewall.

SELECT name FROM student_tbl WHERE REGEXP '[^jz]' ;

8. Match the substring at the end of the word [[:>:]]

Match all data ending with the character "ack" in the title field , Example: Black.

SELECT title FROM movies_tbl WHERE REGEXP 'ack[[:>:]]';

9. Match the substring at the beginning of the word [[:<:]]

Match all data starting with the character "for" in the title field , Example: Forgetting Sarah Marshal.

SELECT title FROM movies_tbl WHERE title REGEXP &#39;[[:<:]]for&#39;;

10. Match character class [:class:]:

ie [:lower:] - lowercase characters, [:digit:] - numeric characters, etc.

Matches all data that contains only alphabetic characters in the title field, for example: stranger things, Avengers.

SELECT title FROM movies_tbl WHERE REGEXP '[:alpha:]' ;

The above is the entire content of this article, I hope it will be helpful to everyone's study. For more exciting content, you can pay attention to the relevant tutorial columns of the PHP Chinese website! ! !

The above is the detailed content of How does MySQL use regular expressions? (code example). For more information, please follow other related articles on the PHP Chinese website!