Introduction to the use of regular expression queries in MySql

Mysql uses the REGEXP keyword to specify the character matching pattern of a regular expression. Next, through this article, I will share with you the method of using regular expression query in MySql. Friends who are interested should take a look.

Regular expressions are commonly used to retrieve and replace text that conforms to 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.

Directory

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

SELECT * FROM user WHERE email REGEXP '^a'

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

2. Character '^' matches records starting with a specific character or string. Records ending in a string

SELECT * FROM user WHERE phone REGEXP '0$'

Character '$' matches records ending with a specific character or string. The above statement queries the records ending with 0 in the mailbox

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

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

Query the records with one character between mailboxes a and c, '.' is equivalent So 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. "|" separates the condition to match the specified string

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

Regular expressions can match the specified string, and use "|" between strings. "Separation.

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

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

"[]" specifies a set, and the above indicates querying the mailbox Mailboxes with a or z or both in them. 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]'

The above matching mailbox does not contain a, b, c, d and does not Contains records 1, 2, and 3.

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

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

means 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.

Summarize

The above is the detailed content of Introduction to the use of regular expression queries in MySql. For more information, please follow other related articles on the PHP Chinese website!