Use the REGEXP keyword
1. Basic Character matching
?
1 2 3 |
SELECT prod_name From Products Where 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 "|"
?
1 2 3 |
SELECT prod_name From Products Where prod_name REGEXP '2017 | 100' |
3. match one of several characters
Specify a group of characters expanded with [and]
?
1 2 3 |
SELECT prod_name From Products Where 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.
?
1 2 3 |
SELECT prod_name From Products Where 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.
?
1 2 3 |
SELECT prod_name From Products Where 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]) |
?
1 2 |
SELECT * FROM 'mytable' Where name REGEXP 'name [[: digit:] '; |
Output name1 name6
7. 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) |
?
1 2 3 |
SELECT prod_name From Products Where prod_name REGEXP '[0-9] {1, 3 }' |
Output 100 15
8. Positioning Operator
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.