MySql Regular Expression Query Method

MySql Regular Expression Query Method

Regular Expressions are often used to retrieve and replace texts that conform to the magic pattern. For example, extract phone numbers from a text file, search for duplicate words in an article, or replace some sensitive words entered by the user. Mysql uses the REGEXP keyword to specify the character matching mode of the regular expression.

1. The character '^' queries 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 preceding statement queries records starting with a in the mailbox.

2. Character 'query records ending with a specific character or string

SELECT * FROM user WHERE phone REGEXP '0$'

The '$' character matches records ending with a specific character or string. The preceding statement queries records whose email address ends with 0.

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

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

The query mailbox a and c have a character record, '.' is equivalent to a placeholder. If it is written as REGEXP 'a. C', that is, there are two vertices between a and c, it indicates 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 m records in all mailboxes.

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

Query records starting with a and ending with m. '*' Indicates zero or more times.

5. Use the "+" character to indicate followed characters

SELECT * FROM user WHERE email REGEXP 'm+'

Query m records in all mailboxes.

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

Query records starting with a and followed by m. '+' Indicates followed by characters.

6. The "|" separator matches the specified string.

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

A regular expression can match a specified string. strings are separated by "|.

7. "[]" indicates that the set matches any 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. It can also be used to match a number set. For example, [0-9] indicates all numbers in the Set range, and [a-z] indicates all letters in the Set range.

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

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

The matched email address does not contain a, B, c, and d, but does not contain records 1, 2, or 3.

9. Use {n,} or {n, m} to specify the number of times a string is connected.

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

B must appear at least twice.

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

Indicates that the string ba appears at least once and at most three times.

Summary

The above section describes how to use regular expression query in MySql. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!