Detailed explanation of query examples using regular expressions in MySql

Regular expressions are commonly used to retrieve and replace text that matches the magic pattern. For example, extract a phone number from a text file, find repeated words in an article, or replace certain sensitive words entered by the user. Mysql uses the REGEXP keyword to specify the character matching pattern of a regular expression. Mysql uses the REGEXP keyword to specify the character matching pattern of a regular expression. Next, I will share with you the method of using regular expression query in MySql through this article. Friends who are interested should take a look at it. I hope it can help you.

1. The character '^' queries for records starting with a specific character or string

SELECT * FROM user WHERE email REGEXP '^a'

The character '^' matches records starting with a specific character or string The above statement queries the records whose mailbox starts with a

2. The character ' queries the records ending with a specific character or string

SELECT * FROM user WHERE phone REGEXP '0$'

The character '$' matches a specific character or string The above statement queries the records whose mailbox ends with 0

3. Use the symbol "."; to replace any character in the string

SELECT * FROM user WHERE email REGEXP 'a.c'

Query between mailbox a and c There is a record of one character, '.' is equivalent to a placeholder. If it is written as REGEXP ‘a..c’, that is, there are two dots between a and c, it means that there must be two characters between a and c in the mailbox.

4. Use "*" to match multiple characters

SELECT * FROM user WHERE email REGEXP 'm*'

Query the records with m in all mailboxes.

SELECT * FROM user WHERE email REGEXP '^am*'

Query the records whose email address starts with the letter a and is followed by the letter m. Where '*' means 0 times or more.

5. Use the character "+" to represent the following character

SELECT * FROM user WHERE email REGEXP 'm+'

Query the records with m in all mailboxes.

SELECT * FROM user WHERE email REGEXP '^am+'

Query the records whose email address starts with the letter a, followed by the letter m. Where '+' means the following character.

6. "|" delimited condition matches the specified string

SELECT * FROM user WHERE email REGEXP 'qq.com|163.com'

Regular expression can match the specified string, and the strings are separated by "|".

7. “[]” indicates that the set matches any one of the specified strings

SELECT * FROM user WHERE email REGEXP '[az]'

”[]” specifies a set. The above indicates that the query mailbox contains a or z or both. All have mailboxes. It can also be used to match a set of numbers. For example, [0-9] represents all numbers in the set interval, and [a-z] represents all letters in the set interval.

8. "[^]" matches characters other than the specified characters

SELECT * FROM user WHERE email REGEXP '[^a-d1-3]'

As shown above, the matching mailbox does not contain a, b, c, d and does not contain 1, 2, 3 records.

9. Use {n,} or {n,m} to specify the number of occurrences of string concatenation

SELECT * FROM user WHERE email REGEXP 'b{2}'

means that the letter b appears at least 2 times.

SELECT * FROM user WHERE email REGEXP 'yu{1,3}'

means that the string ba appears at least once and at most 3 times.

Related recommendations:

WeChat applet implements simple input regular expression verification function sharing

How to use regular expressions in Javascript

Regular Expression_Regular Expression in Replace

The above is the detailed content of Detailed explanation of query examples using regular expressions in MySql. For more information, please follow other related articles on the PHP Chinese website!