Regular expressions in MySQL

Regular expressions are created using regular expression language.

Matching of basic characters

. is a special character in a regular expression language that represents a match for any one character

There is an important difference between like and regexp, like matches the entire column, and if the matched text appears only in the column values, like will not find it and the corresponding row will not be returned (unless wildcard characters are used)

The regexp matches within the column values, and if the matched text appears in the column values, RegExp will find it and the corresponding row will be returned.

Regular expressions in MySQL match the default case-insensitive, case-sensitive, using the binary keyword, followed by the binary regexp

Make or Match

| is the OR operator of a regular expression that represents one of the matching

Match one of several characters

[] is another form of an or statement. In fact, the regular expression [123] ton is the abbreviation for [1|2|3] ton, which is equivalent, but requires [] to define what the or statement looks for, such as the following example

Not the output we expect, this is because MySQL assumes that you mean ' 1 ' or ' 2 ' or ' 3 ton ' unless the character | is enclosed in a collection and is responsible for the entire string.

Match Range

[1-5] defines a range, the range of the expression is matched 1 to 5, can also be written as [12345], the two are equivalent, but the former is more tidy

Match Special characters

\ \. Match. The escape character, in order to match the backslash (\) character itself, requires the use of the \\\

Most regular expression implementations use a single backslash to escape special characters so that the characters themselves can be used, but MySQL requires two backslashes, which MySQL interprets one by itself, and the regular expression interprets the other

Match multiple instances

Metacharacters description

* 0 or more matches

+ 1 or more matches, equals {1,}

？ 0 or 1 matches, equals {0,1}

{n} specified number of matches

{n,} is greater than or equal to the specified number of matches

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

To explain this regular expression, the escape character matches the parentheses, [0-9] matches the number, s? Make s selectable, 0 or 1 x

When using regular expressions, it's almost always more than one way to write a particular expression

Locator characters

Metacharacters description

^ Beginning of the text

The end of the $ text

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

[[:]: The end of the word

Note ^ There are two uses, in the set (defined by [and]), to negate the collection, otherwise, to match the beginning of the string

Matches a number or point.

Simple regular-expression testing

Regular expressions in MySQL