Shulou(Shulou.com)05/31 Report--

This article shares with you about the methods of using regular expression queries in MySql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Regular expressions are often used to retrieve and replace text that matches the magic pattern. For example, extract a phone number from a text file, find repetitive words in an article, or replace some sensitive words entered by the user. Mysql uses the REGEXP keyword to specify character matching patterns for regular expressions. Mysql uses the REGEXP keyword to specify character matching patterns for regular expressions. Next through this article to share with you the use of regular expression query methods in MySql, interested friends to take a look at it, I hope it can help you.

1. The character'^ 'queries 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 that start with an in the mailbox.

two。 Character 'queries a record that ends with a specific character or string

SELECT * FROM user WHERE phone REGEXP'0 $'

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

3. Replace any character in a string with the symbol "."

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

There is an one-character record between query mailbox an and c,'.' It's equivalent to a placeholder. If it is written as REGEXP'a.. c', that is, there are two dots between an and c, it means that there should be two characters between an and c in the mailbox.

4. Use "*" to match multiple characters

SELECT * FROM user WHERE email REGEXP'masks'

Query all mailboxes with m records.

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

Query the mailbox with the letter a, followed by the letter m. Where'* 'means 0 or more times.

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

SELECT * FROM user WHERE email REGEXP'masks'

Query all mailboxes with m records.

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

Query the records that begin with the letter a, followed by the letter m. Where'+ 'means to follow the character.

6. "|" the separation condition matches the specified string

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

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

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

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

"[]" specifies a collection, which indicates that the query mailbox has mailboxes with an or z or both. It can also be used to match a set of numbers, for example, [0-9] represents all the numbers in the set interval, and [amurz] represents all the letters in the set interval.

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

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

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

9. Specify the number of times a string concatenation occurs using {n,} or {nrecom}

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

Indicates that the letter b appears at least twice.

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

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

Thank you for reading! This is the end of this article on "the method of using regular expression query in MySql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.