MYSQL uses regular expressions to filter data. mysql Regular Expressions

MYSQL uses regular expressions to filter data. mysql Regular Expressions

I. Differences between regular expressions and LIKE expressions
The Regular Expression of Mysql only allows a subset of the SQL language to match basic characters and strings.
For example, select * from wp_posts where post_name REGEXP 'hello' can retrieve all rows in the post_name column that contain hello

REGEXP '. og'. Is a special character in a regular expression. It indicates matching a character. Therefore, both dog, hog, and mog can match.

Note:

Difference between LIKE and REGEXP: LIKE matches the entire column.If the matched text only appears in the column Value, LIKE does not find it, and the corresponding rows will not be returned (unless Wildcards are used ). While REGEXP matches the column value. If the matched text appears in the column value, REGEXP will find it and the corresponding row will be returned, at this time, there is a very important difference (of course, if you adapt to positioning symbols ^ and $, You can implement REGEXP matching the entire column rather than the subset of the column ).

Case sensitivity: Regular Expression matching in MySQL (after version 3.23.4) is case insensitive. To be case sensitive, use the BINARY keyword, such as where post_name regexp binary 'hello. 000'

Ii. Basic Character matching
Retrieve all rows in which the column prod_name contains text 1000:

OR matching
Equivalent to: or operation "|"

Match one of several characters
Only match specific characters. You can specify a group[And]To complete.

[456]Defines a group of characters, which means matching 4 or 5 or 6.[]Is another form of OR statement.[456]Yes[4|5|6]. Matching range
[1-3] a-zAre valid.

Match special characters

The Regular Expression Language consists of special characters with special meanings.

.Indicates matching any character in a regular expression.

For exampleonString line:

Then how to match.,[],|,-?

To match special characters, you must use\\. For example\\.Search·

Matching character class

Match multiple instances

For exampleMatch the four digits that are connected together:

sticks?:sAfter？Make s optional, because？Match the first occurrence of any character 0 or 1 occurrence.

[:digit:]Matches any number, so it is a set of numbers.{4}Specifically, the first character must appear four times.
So[:digit:]{4}Match any four digits that are connected together.

3. Positioning Operator
All examples so far match any text in a string. To match the specific text so far, you need to use a locator.

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

SELECT to test the regular expression. The REGEXP check always returns 0 or 1,

SELECT 'hello' REGEXP '0' // return 0

The above is all the content of this article, hoping to help you learn.