Mysqlsql Statement Review 2 -- Regular Expression

Mysqlsql Statement Review 2 -- Regular Expression

Here, we will briefly review the usage of Regular Expressions in mysql when filtering data. This mainly refers to an example for each usage or a brief description.

The REGEXP keyword is used to filter data using regular expressions.

Basic character match:

SELECT prod_name FROM products WHERE prod_name REGEXP '201312 ';

This query statement indicates that the prod_name field contains 1000 of data, as long as any part of the value contains 1000

SELECT prod_name FROM products WHERE prod_name REGEXP '. 000 ';

". "Represents a single-character wildcard, which is equivalent to any single character, similar to the wildcard" _ "in LIKE. This statement means that the prod_name field in the query contains, for example, or a single character is followed by 000 data records.

OR match:

SELECT prod_name FROM products WHERE prod_name REGEXP '2017 | 100 ';

Here, "|" indicates or, that is, "or". This statement will query data that contains 1000 or 2000 or both in the prod_name field, or matching can give more than two matching items, such as 1000 | 2000 | 3000

Match one of the following characters:

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton ';

[] The enclosed part indicates matching one of them, such as 1 Ton and 2 Ton.

Matching range:

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-9] Ton ';

If the characters in the [] section in the previous example are long and both are arrays, and the numbers are continuous, you can change them to range match, so that the SQL statement will be shorter, of course, you can replace [1-9] with [123456789]. If it is a letter, you can use [a-z ].

Match special characters:

SELECT prod_name FROM products WHERE prod_name REGEXP '\\.';

To match special characters in mysql, you must add // escape characters, because special characters have special meanings in SQL statements.

Match multiple instances:

First, we need to introduce several matching characters.

* 0 or multiple matches

+ One or more matches (equal to {1 ,})

? 0 or one matching (equal to {0, 1 })

{N} specified number of matches

{N ,}no less than a specified number of matches

The range of {n, m} matches (m cannot exceed 255)

SELECT prod_name FROM products WHERE prod_name REGEXP '\ ([0-9] sticks? \\)';

In this statement? Matches zero or one occurrence of any character before it

OPERATOR:

^ Match the beginning of the text

$ Match the end of the text

[[: <:] Start of a word

[[: >:]] End of a word

SELECT prod_name FROM products WHERE prod_name REGEXP '^ [0-9 \.]';

The matched string indicates that only data starting with. And any number is queried.