How to use regular expression query in MySql

Regular expressions are often used to search and replace text that matches a certain pattern. For example, extracting phone numbers from a text file, finding repeated words in an article, or replacing certain sensitive words entered by the user. MySQL uses the REGEXP keyword to specify a regular expression character matching pattern.

1. The character '^' searches for records that begin with a specific character or string

SELECT * FROM user WHERE email REGEXP '^a'

The character '^' matches records that begin with a specific character or string. The above statement queries records whose mailboxes begin with a

2. Characters' 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 above statement queries 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 a character between mailboxes a and c. '.' 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 all mailboxes containing m.

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

Query the mailboxes that start with letter a and are followed by letter m. Where '*' means 0 or more times.

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

SELECT * FROM user WHERE email REGEXP 'm+'

Query all mailboxes containing m.

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

Query the mailboxes that start with the letter a, followed by the letter m. Where '+' means the character that follows.

6. “|” separates the conditions to match the specified string

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

Regular expressions can match specified strings, and strings are separated by "|".

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

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

"[]" specifies a set. The above means querying mailboxes with a or z or both. It can also be used to match a set of numbers, for example, [0-9] means all numbers in the set, and [az] means all letters in the set.

8. “[^]” matches any character other than the specified character

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

The above matches the records that do not contain a, b, c, d and do not contain 1, 2, 3 in the mailbox.

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

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

Indicates that the letter b appears at least 2 times.

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

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

Summarize

The above is the method of using regular expression query in MySql introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!