[MySQL] searching with regular expressions

The function of a regular expression is to match text and to compare a pattern (regular expression) with a text string.

Basic character Matching

SELECT column name from table name WHERE column name REGEXP condition ORDER by column name;

The form is the same as like, except that the condition followed by the regexp is a regular expression.

Difference:

Like matches the entire column. If the matched text appears in the column values, like does not find it, and the corresponding row is not returned (unless wildcard characters are used).

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

Regular expressions are case-insensitive and use the binary keyword if you need to differentiate. such as REGEXP BINARY + regular expression conditions.

Make or Match

Use regular expression notation ' | ' The condition is tied.

For example, OR 2000 = 1000|2000

Match one of several characters

Use [] to define a set of characters

For example [123]ABC = 1ABC or 2abc or 3ABC

[123]ABC = [1|2|3]ABC

Match anything except the specified character

[^123] matches anything except 1\2\3.

Match Range

[0123456789] = [0-9]

Match Special characters

‘.‘ Match any character

\\-means Find-

\\. Represents a lookup.

\\\ means find \

　　

Match character class

Class description

[: alnum:] Any letter and number [a-za-z0-9]

[: Alpha:] Any character [a-za-z]

[: Blank:] spaces and tables [\\t]

[: Cntrl:] ASCII control characters (ASCII 0 to 31 and 127)

[:d igit:] any number [0-9]

[: Graph:] Same as [:p rint:], but not with spaces

[: Lower:] Any lowercase letter [a-z]

[:p rint:] any printable character

[:p UNCT:] Neither [: alnum:] Nor any of the characters in [: Cntrl:]

[: space:] Any white space character including spaces [\\f\\n\\r\\t\\v]

[: Upper:] Any capital letter [A-z]

[: xdigit:] Any hexadecimal number [a-fa-f0-9]

Match multiple instances

Repeating meta characters

Metacharacters description

* 0 or more matches

+ 1 or more matches {1,}

? 0 or 1 matches {0, 1}

{n} specified number of matches

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

{n, m} matches the range of numbers (m not more than 255)

Locator characters

Matches text at the specified position

Positional meta-characters

Metacharacters description

^ Beginning of the text

The end of the $ text

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

[[:]: The end of the word

^ There are two uses, in the set (with [and] definition), to negate the collection, otherwise, to refer to the beginning of the string

　　

[MySQL] searching with regular expressions