MySQL Regular expression use

Using the RegExp keyword

1, basic character matching

?

1 2 3

SELECT Prod_name from the products Where prod_name REGEXP '. 000 '

Like and regexp difference

Like will match this column, and RegExp will match within the column value

In MySQL, the expression is case-insensitive and distinguishes between using the BINARY keyword, such as where Prod_name REGEXP BINARY ' jet.000 '

2. Make or Match

Use the "|"

?

1 2 3

SELECT Prod_name from the products Where prod_name REGEXP ' 1000|2000 '

3. Match one of several characters

Specify a set of characters to be enlarged with [and]

?

1 2 3

SELECT Prod_name from the products Where prod_name REGEXP ' [123]ton '

Output

1ton

2ton

4. Matching Range

[0-9] number 0 to 9

[A-z]a to Z

[A-z] A to Z

[^0-9] ^ means not, that is, the match is not 0-9

Note that the back must be bigger than the front.

?

1 2 3

SELECT Prod_name from the products Where prod_name REGEXP ' [0-9]ton '

5. Matching special characters

In the special character before the "" to escape, note that in general, the escape of regular expressions plus a "" on it, in MySQL need to add two

?

1 2 3

SELECT Prod_name from the products Where prod_name REGEXP '. 000 '

Output

1.000ton

6, matching character class (POSIX character class)

You need to add a layer outside [], for example [[:d igit:]]

	description
[:alnum:]	any letter and number (same [a-za-z0-9 ]
[:alpha:]	any letter (same [a-za-z])
[:blank:]	spaces and tables (same as [t])
[:cntrl:]	ascii control characters (ASCII0 to 31 and 127)
any number (with [0-9])
is the same as [:p Rint:]] but does not contain spaces
any lowercase letter (with [A-z])
any printable character
[:p unct:]
any white space characters, including spaces (same as [FNRTV])
any uppercase letters (with [A-z])
any 16 binary digits (same [a-fa-f0-9])

?

1 2	SELECT * from ' mytable ' Where name REGEXP ' name[[:d igit:] ';

Output name1 Name6

7. Match multiple instances

Metacharacters	Description
*	0 or more matches
+	1 or more matches
？	0 or 1 matches
N	Specified number of matches
{N,}	Not less than the specified number of matches
{N,m}	Match number of ranges (m not more than 255)

?

1 2 3

SELECT Prod_name from the products Where prod_name REGEXP ' [0-9]{1,3} '

Output 100 15

8, locator character

Metacharacters	Description
^	The beginning of the text
$	End of text
[[:;:]]	The beginning of the word
[[::]]	End of the word

Note that ^ has two usages, a right and wrong, one is the beginning of the text, in [] in the expression is not, otherwise is the beginning of the text.