MySQL uses regular expressions to filter data

The difference between a regular and a like
MySQL's regular expression simply makes a subset of the SQL language, which can match basic characters, strings.
For example: SELECT * from wp_posts where post_name REGEXP ' Hello ' to retrieve all rows containing hello in the dequeue post_name

REGEXP '. og ' is a special character in a regular expression. It means matching one character, so dog,hog,mog and so on can match.

Attention:

About the difference between like and regexp: like matches the entire column. if the matched text appears only in the column values , like does not find it, and the corresponding row does not return (except, of course, wildcards). While regexp matches within the column values, if the matching matched text appears in the column values, RegExp will find it, and the corresponding row is returned, a very important difference (of course, if the positional symbols ^ and $ are adapted, you can implement the regexp to match the entire column rather than a subset of the columns).

About case sensitivity: MySQL expression matching (from version 3.23.4) is case insensitive. If you want to distinguish case, you should use the binary keyword, such as where post_name REGEXP binary ' hello.000 '

Second, basic character matching
Retrieves a column prod_name all lines that contain text 1000:

Make or Match
Equivalent to: or operation "|"

Match one of several characters
Just want to match a specific character. You can do this by specifying a set [ ] of characters that are enclosed and used.

[456]Defines a set of characters that he means to match 4 or 5 or 6. []is another form of an or statement. [456]is [4|5|6] the abbreviation. Match Range
[1-3]a-zis a legal range.

Match Special characters

The regular expression language is composed of special characters with special meanings.

.Matches any one character in a regular expression

For example, matches on a line that includes a string in Prod_name:

How does that match,, . [] | , - ?

In order to match Special characters, it must be used \\ as leading. For example, to \\. find·

Match character class

Match multiple instances

Another example is a matching 4-digit number :

sticks?: s after ？ making s optional, because ？ it matches 0 or 1 occurrences of any character immediately preceding it.

[:digit:]Matches any number, thus it is a set of numbers. {4}the character that precedes it is exactly required to appear 4 times.
So [:digit:]{4} match any 4-bit numbers that are connected together.

Third, locator
All the examples so far have been matched to any text in a string so far. To match a specific text, you need to use a locator.

You can also test the correctness of regular expressions without using a database:

SELECT to test the regular expression, the RegExp check always returns 0 or 1,

SELECT ' HELLO ' REGEXP ' 0 '//return to 0

The above is the whole content of this article, I hope that everyone's study has helped.

Original address: http://www.link588.com/html/shujuku/134960.html

MySQL uses regular expressions to filter data