MySQL regular expression-MySQL series (4)

Use REGEXP keyword 1. Basic Character matching

SELECT prod_nameFrom ProductsWhere prod_name REGEXP'.000'

Difference between LIKE and REGEXP

LIKE matches this column, while REGEXP matches the column value.

Regular Expressions in MySQL are case-insensitive. BINARY keywords must be used to distinguish them, such as Where prod_name regexp binary 'Jet. 000'

2. Perform OR matching

Use "|"

SELECT prod_nameFrom ProductsWhere prod_name REGEXP'1000|2000'

3. match one of several characters

Specify a group of characters expanded with [and]

SELECT prod_nameFrom ProductsWhere prod_name REGEXP'[123]ton'

Output
1ton
2ton

4. Matching range

[0-9] numbers 0 to 9
[A-z] a to z
[A-Z] A to Z

[^ 0-9] ^ indicates no, that is, the matching is not 0-9
Note: The following must be larger than the previous one.

SELECT prod_nameFrom ProductsWhere prod_name REGEXP'[0-9]ton'

5. Match special characters

Add "\" before special characters to escape. Note that you can add "\" to the escape of the regular expression under normal circumstances. You need to add two to MySQL.

SELECT prod_nameFrom ProductsWhere prod_name REGEXP'\\.000'

Output
1.000ton

6. Matching character class (Posix character class)

When using this function, you need to add a layer [], for example, [[: digit:].

Class	Description
[: Alnum:]	Any letter and number (same as [a-zA-Z0-9])
[: Alpha:]	Any letter (same as [a-zA-Z])
[: Blank:]	Space and tabulation (same as [\ t])
[: Cntrl:]	ASCII control characters (ASCII0 to 31 and 127)
[: Digit:]	Any number (same as [0-9])
[: Graph:]	It is the same as [[: print:], but does not contain spaces.
[: Lower:]	Any lowercase letter (same as [a-z])
[: Print:]	Any printable character
[: Punct:]	That is, it is not a character in [[[: alnum:] nor in [[: cntrl :]
[: Space:]	Any blank characters including spaces (same as [\ f \ n \ r \ t \ v])
[: Upper:]	Any uppercase letter (same as [A-Z])
[: Xdigit:]	Any hexadecimal number (same as [a-fA-F0-9])

SELECT * FROM `mytable`Where name REGEXP'name[[:digit:]]';

Output name1 name67, matching multiple instances

Metacharacters	Description
*	0 or multiple matches
+	One or more matches
?	0 or 1 matching
{N}	Match of the specified number
{N ,}	Match of no less than a specified number
{N, m}	Number of matches (m cannot exceed 255)

SELECT prod_nameFrom ProductsWhere prod_name REGEXP'[0-9]{1,3}'

Output 100 158, positioning character

Metacharacters	Description
^	Start of Text
$	End of Text
[[: <:]	Start of Word
[[:>:]	End of a word

Note: ^ has two usage methods: one is not, the other is the beginning of the text, and the other is represented in []. Otherwise, it is the beginning of the text.