MySQL regular expression-MySQL series (IV) _ MySQL

MySQL regular expressions-MySQL series (4) regular expressions

BitsCN.com use the 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 a special character to escape. Note that you can add a "/" to the escape of the regular expression under normal circumstances. in MySQL, you need to add two

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:] is the same as [[: print:], but does not contain spaces [: lower:] any lowercase letter (same as [a-z]) [: print:] any printable character [: punct:] is neither [[: alnum:] nor [[: the character [: space:] in cntrl:] any blank characters including spaces (same as [// f // n // r // t // v]) [: upper:] any capital letter (same as [A-Z]) [: xdigit:] Any hexadecimal number (same as [a-fA-F0-9])

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

The output name1 name67 matches multiple instance metacharacters. * 0 or multiple matches + 1 or multiple matches? 0 or 1 matches {n} specified number matches {n,} not less than specified number matches {n, m} number range (m cannot exceed 255)

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

Output 100 158, positioning element description ^ start of the text $ end of the text [[: <:] start of the word [[: >:]] end of the 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.

BitsCN.com