Mysql SQL Statement Review 2--Regular expression

Here's a brief look at MySQL's use of regular expressions when filtering data, primarily for each usage, or to add a few short notes.

This is to say in advance that using regular expressions to filter the data is using the REGEXP keyword

Basic character Matching:

SELECT Prod_name from Products WHERE prod_name REGEXP ' 1000 ';

The query statement means that the Prod_name field contains 1000 of the data, as long as any part of the value contains 1000 can be

SELECT Prod_name from Products WHERE prod_name REGEXP '. 000 ';

"." Represents a single character wildcard, equivalent to any single character, similar to the wildcard character "_" in like, which means that the Prod_name field in the query contains data such as 1000,2000, or any character followed by 000.

To do or match:

SELECT Prod_name from Products WHERE prod_name REGEXP ' 1000|2000 ';

Here "|" means the meaning of or, that is, "or" means that the statement will be the Prod_name field contains 1000 or contains 2000 or two contains data query out, or match can give more than two matches such as 1000|2000|3000

Match one of several characters:

SELECT Prod_name from Products WHERE prod_name REGEXP ' [123] Ton ';

[] The enclosed part represents a match for one of the 1 ton, 2 ton such

Match range:

SELECT Prod_name from Products WHERE prod_name REGEXP ' [1-9] Ton ';

If the characters in the previous example [] are longer and are arrays, and the numbers are contiguous, they can be changed to a range match, so that the SQL statement will be a little shorter, of course [1-9] can be replaced by [123456789], and if it is a letter, use [A-z] Such a way

Match Special characters:

SELECT Prod_name from Products WHERE prod_name REGEXP ' \ \. ' ；

You need to add//escape to match Special characters in MySQL, because special characters have special meanings in SQL statements

Match multiple instances:

First, we need to introduce several matching characters

* 0 or more matches

+ one or more matches (equals {1,})

? 0 or one match (equals {0,1})

{n} specified number of matches

{n,} not less than a specified number of matches

{N,m} matches the number of ranges (m not exceeding 255)

SELECT Prod_name from Products WHERE prod_name REGEXP ' \ \ ([0-9] sticks?\\) ';

In this statement? Match 0 or more occurrences of any character preceding it

Locator:

^ Start of matching text

$ match end of text

[[: <:]] The beginning of the word

[[:]: The end of the word

SELECT Prod_name from Products WHERE prod_name REGEXP ' ^[0-9\\.] ' ；

The matching string means that only data that begins with. And any number is queried

Mysql SQL Statement Review 2--Regular expression