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

This paper summarizes the use of regular expressions for query operations in MySQL. Share with you for your reference, the details are as follows:

The character "^" matches a specific character

SELECT * FROM fruits WHERE f_name REGEXP'^ b'

The character'$'ends with a specific character

SELECT * FROM fruits WHERE f_name REGEXP 'yearly'

Character "." Replace any character in a string

SELECT * FROM fruits WHERE f_name REGEXP 'a.g'

The asterisk "*" matches the preceding characters any number of times, including 0. The plus sign "+" matches the preceding character at least once.

SELECT * FROM fruits WHERE f_name REGEXP'^ ba*';SELECT * FROM fruits WHERE f_name REGEXP'^ ba+'

Matches the specified string

SELECT * FROM fruits WHERE f_name REGEXP 'on';SELECT * FROM fruits WHERE f_name REGEXP' on | ap'

Matches any of the specified strings

SELECT * FROM fruits WHERE f_name REGEXP'[ot]'; SELECT * FROM fruits WHERE s_id REGEXP'[456]'

"[^ character set]" matches any characters that are not in the specified collection

SELECT * FROM fruits WHERE f_id REGEXP'[^ a-e1-2]'; SELECT * FROM fruits WHERE f_name REGEXP'x {2,}'; SELECT * FROM fruits WHERE f_name REGEXP'ba {1mer3}'

[example .68] in the fruits table, query records that start with the letter'b'in the f_name field

SELECT * FROM fruits WHERE f_name REGEXP'^ b'

[example .69] in the fruits table, query records whose f_name fields begin with "be"

SELECT * FROM fruits WHERE f_name REGEXP'^ be'

[example. 70] in the fruits table, query records that end with the letter't'in the f_name field

SELECT * FROM fruits WHERE f_name REGEXP 'yearly'

[example. 71] in the fruits table, query records whose f_name field ends with the string "rry"

SELECT * FROM fruits WHERE f_name REGEXP 'rry$'

[example. 72] in the fruits table, the query f_name field value contains the letters'a 'and' g 'and has only one letter between the two letters

SELECT * FROM fruits WHERE f_name REGEXP 'a.g'

[example. 73] in the fruits table, the query f_name field value begins with the letter'b 'and the letter' a 'appears after the' b'.

SELECT * FROM fruits WHERE f_name REGEXP'^ ba*'

[example. 74] in the fruits table, query records where the f_name field value begins with the letter'b 'and followed by the letter' a'at least once

SELECT * FROM fruits WHERE f_name REGEXP'^ ba+'

[example .75] in the fruits table, query records whose f_name field value contains the string "on"

SELECT * FROM fruits WHERE f_name REGEXP 'on'

[example. 76] in the fruits table, query records whose f_name field value contains the string "on" or "ap"

SELECT * FROM fruits WHERE f_name REGEXP'on | ap'

[example .77] in the fruits table, use the LIKE operator to query records with a f_name field value of "on"

SELECT * FROM fruits WHERE f_name LIKE 'on'

[example .78] in the fruits table, look for records that contain the letters o or t in the f_name field

SELECT * FROM fruits WHERE f_name REGEXP'[ot]'

[example. 79] in the fruits table, query records that contain 4, 5, or 6 values in the s_id field

SELECT * FROM fruits WHERE s_id REGEXP'[456'

[example. 80] in the fruits table, query records that contain characters other than the letters a to e and the numbers 1 to 2 in the f_id field

SELECT * FROM fruits WHERE f_id REGEXP'[^ a-e1-2]'

[example. 81] in the fruits table, records that query the value of the f_name field with the string'x' at least twice

SELECT * FROM fruits WHERE f_name REGEXP'x {2,}'

[example. 82] in the fruits table, the string "ba" appears in the query f_name field value at least once and up to 3 times.

SELECT * FROM fruits WHERE f_name REGEXP'ba {1pr 3}'

PS: here are two more convenient regular expression tools for your reference:

JavaScript regular expression online testing tool:

Http://tools.jb51.net/regex/javascript

Regular expression online generation tool:

Http://tools.jb51.net/regex/create_reg

More readers who are interested in MySQL-related content can check out this site's special topics: "A Summary of MySQL Common functions", "A Collection of MySQL Log Operation skills", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.