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

I would like to share with you the case study of mysql regular expressions. I hope you will gain a lot after reading this article. Let's discuss it together.

MySQL supports another pattern matching operation based on regular expressions and REGEXP operators.

1. It provides powerful and flexible pattern matching that can help us implement power search utilities for database systems.

2.REGEXP is the operator used when performing regular expression pattern matching.

3.RLIKE is a synonym. 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.

What does the PATTERN pattern match * zero or more string instances before it + one or more string instances before it. Any character? Matches zero or one instance of the previous string. The ^ caret (^) matches the beginning of the string $string ends any character listed between [abc] square brackets [^ abc] any character [Amurz] that is not listed 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 a character class, that is, [: alpha:] matches letters, [: space:] matches spaces, [: punct:] matches punctuation, and [: upper:] matches upper letters. P1 | p2 | p3 rotation; matches any instance of the preceding element of pattern p1Magee p2 or p3 {n} m to n instances of the preceding element

Examples are as follows:

Match the beginning of the string (^):

Give all names that start with "sa". Example-sam,samarth.

SELECT name FROM student_tbl WHERE name REGEXP'^ sa'

Matches the end of the string ($):

Give all names that end with "on". Example-norton,merton.

SELECT name FROM student_tbl WHERE name REGEXP 'on$'

Match zero or one instance of the string preceding it:

Give all headings that contain "com". Example-comedy, romantic comedy.

SELECT title FROM movies_tbl WHERE title REGEXP 'com?'

Match any pattern in p1, p2, or p3 (p1 | p2 | p3):

Give all names that contain "be" or "ae". Example-Abel, Baer.

SELECT name FROM student_tbl WHERE REGEXP'be | ae'

Match any characters listed in formula brackets ([abc]):

Give all names that contain "j" or "z". Example-Lorentz, Rajs.

SELECT name FROM student_tbl WHERE REGEXP'[jz]'

Match'a'to'z'- ([a murz]) ([a murz] and (.) Any lowercase letters between:

Retrieve all names in the range of the letters "b" and "g", followed by any character, followed by the letter "a". For example, Tobias, sewall.

Match any single character (.)

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

Matches any characters not listed in square brackets. ([^ abc]):

Give all names that do not contain "j" or "z". For example: nerton, sewall.

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

Match word ending [[: >:]]:

Give all titles that end with the character "ack". Example-Black.

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

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