Shulou(Shulou.com)06/01 Report--

The following content mainly brings you MySQL regular expression explanation, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

MySQL supports matching operations based on another pattern of regular expressions and regexp operators. The following article will take you to know the regular expressions (Regexp) of MySQL, and briefly introduce how to use regular expressions (Regexp) in MySQL through examples. I hope it will be helpful to you.

A brief introduction to MySQL regular expression (Regexp)

1. It provides powerful and flexible matching patterns, which can help us to implement powerful search utilities for database systems.

2. Regexp is the operator used to perform regular expression pattern matching, and rlike is synonymous.

3. It also supports many metacharacters that provide more flexibility and control when performing pattern matching.

4. the backslash is used as an escape character. If a double backslash is used, it is considered only in pattern matching.

5. Case insensitive.

The following is a table of regular patterns that can be used with the REGEXP operator. [recommended video tutorials: MySQL video tutorials]

The pattern description (what exactly matches) ^ matches the starting position of the string. $matches the end of the string. . Matches any single character except "\ n". [...] Matches any of the characters contained. [^.] Matches any characters that are not included. ? Matches the zero or one subexpression (string) before it. * matches zero or more subexpressions (strings) that precede it. + matches one or more subexpressions (strings) that precede it. [abc] match any characters listed between formula parentheses. [^ abc] matches any characters not listed between formula parentheses. [Amurz] matches any uppercase letter. [amurz] matches any lowercase letter. [0-9] matches any number from 0 to 9. [[:]] matches the end of the word. [: class:] matches character classes, that is, [: alpha:] matches letters, [: space:] matches spaces, [: punct:] matches punctuation marks, and [: upper:] matches upper letters. P1 | p2 | p3 matches any pattern. P1 or p2 or p3 {n} matches the subexpression {mforce n} of the element preceding n. Matches the n subexpression of m to the preceding element

Examples of use:

Here is a simple example to introduce the use of MySQL regular expressions (Regexp).

1. Match the beginning of the string (^):

Matches all data that the name field begins with 'sa', for example: Sample-sam,samarth.

SELECT name FROM student_tbl WHERE name REGEXP'^ sa'

2. Match the end of the string ($):

Matches all data in the name field that ends with 'on', for example: norton,merton.

SELECT name FROM student_tbl WHERE name REGEXP 'on$'

3. Matches the zero of the string before it or a subexpression (? ):

The matching title field contains all the data of 'com', for example: comedy, romantic comedy.

SELECT title FROM movies_tbl WHERE title REGEXP 'com?'

4. Match any pattern p1 or p2 or p3 (p1 | p2 | p3):

The matching name field contains all the data for 'be' or' ae', for example: Abel,Baer.

SELECT name FROM student_tbl WHERE REGEXP'be | ae'

5. Any characters listed between formula parentheses ([abc]):

The matching name field contains all the data for'j' or'z', for example: Lorentz,Rajs.

SELECT name FROM student_tbl WHERE REGEXP'[jz]'

6. Match any single lowercase letter between'a 'and' z'([amurz] and (.)):

The first bit in the matching name field contains the letters in the "b" and "g" ranges, the second bit contains any characters, and the third bit contains all string data with the letter'a', for example: Tobias,sewall.

Match any single character (.)

SELECT name FROM student_tbl WHERE REGEXP'[bMurg]. [a]'

7. Any characters not listed between the parentheses ([^ abc]):

Matches all names that do not contain'j' or'z'. Example: nerton,sewall.

SELECT name FROM student_tbl WHERE REGEXP'[^ jz]'

8. Match the substring at the end of the word [[: >:]]

Matches all data in the title field that ends with the character "ack", for example: Black.

SELECT title FROM movies_tbl WHERE REGEXP 'ack [[: >:]]'

9. The substring that matches the beginning of the word [[:

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.