Detailed example of mysql regular expression (Regexp)

MySQL supports another pattern matching operation based on regular expressions and the REGEXP operator. (Related recommendations: "MySQL Tutorial")

1. It provides powerful and flexible pattern matching, which can help us achieve power for the database system. Search utility.

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

3.RLIKE is a synonym. It also supports a number of metacharacters that provide greater flexibility and control when performing pattern matching.

4. Backslash is used as an escape character. If double backslashes are used, they are only considered in pattern matching.

5. It is not case sensitive.

##[[:<:]] Matches the beginning of a word. [[:>:]] Matches the end of a word. [:class:] matches a character class, that is, [:alpha:] matches letters, [:space:] matches spaces, [:punct:] Matches punctuation, [:upper:] matches upper letters. p1|p2|p3rotation; matches any pattern p1, p2 or p3{n}nInstances of the previous element{m,n}m to n instances of the previous element

Example:

Match the beginning of the string (^):

Gives all names starting with "sa". Example – sam, samarth.

SELECT name FROM student_tbl WHERE name REGEXP '^sa';

Matches the end of the string ($):

gives all names ending with "on". Example - norton, merton.

SELECT name FROM student_tbl WHERE name REGEXP 'on$';

Matches zero or one instance of the string preceding it (?):

gives all titles containing "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):

gives all the patterns containing "be" ” or “ae” name. Example - Abel, Baer.

SELECT name FROM student_tbl WHERE REGEXP 'be|ae' ;

matches any character listed in square brackets ([abc]):

given contains "j" or "z" ” all names. Example - Lorentz, Rajs.

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

Matches any lowercase letter between ' a ' to ' z ' - ([a-z]) ([a-z] and (.):

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

Matches any single character (.)

SELECT name FROM student_tbl WHERE REGEXP '[b-g].[a]' ;

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

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

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

Match word ending [[:>:]]:

gives all titles ending with the characters "ack". Example - Black.

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

Match words starting with [[:<:]]:

Gives all titles starting with the character "for". Example - Forgetting Sarah Marshal.

SELECT title FROM movies_tbl WHERE title REGEXP &#39;[[:<:]]for&#39;;

matches a character class [:class:]:

i.e [:lower:] - lowercase characters, [:digit:] - digits Characters, etc.

Only give all titles containing alphabetic characters. Examples-stranger things, Avengers.

SELECT title FROM movies_tbl WHERE REGEXP '[:alpha:]' ;

This article is a detailed example of mysql regular expression (Regexp), I hope Please help those who need it!

PATTERN	What does the pattern match?
*	before it Zero or more string instances
	preceded by one or more string instances
.	Any character
?	matches zero or one instance of the preceding string.
^	The caret (^) matches the beginning of the string
$	String End
[abc]	Any characters listed between square brackets
[^abc]	Any characters not listed between square brackets
[A-Z]	matches any uppercase letter.
[a-z]	Matches any lowercase letter
[0-9]	Matches from Any number from 0 to 9.

The above is the detailed content of Detailed example of mysql regular expression (Regexp). For more information, please follow other related articles on the PHP Chinese website!