MariaDB 正则

MariaDB 正则

匹配规则

----------------------------------------------------------------

Class      Description

----------------------------------------------------------------

[:alnum:]  Any letter or digit, (same as [a-zA-Z0-9])

[:alpha:]  Any letter (same as [a-zA-Z])

[:blank:]  Space or tab (same as [\t ])

[:cntrl:]  ASCII control characters (ASCII 0 through 31 and 127) 

[:digit:]  Any digit (same as [0-9])

[:graph:]  Same as [:print:] but excludes space

[:lower:]  Any lowercase letter (same as [a-z])

[:print:]  Any printable character

[:punct:]  Any character that is neither in [:alnum:] nor [:cntrl:]

[:space:]  Any whitespace character including the space (same as [\f\n\r\t\v ])

[:upper:]  Any uppercase letter (same as [A-Z])

[:xdigit:] Any hexadecimal digit (same as [a-fA-F0-9]) 

----------------------------------------------------------------

------------------------------------------------------

Metacharacter Description

------------------------------------------------------

* 0 or more matches

+ 1 or more matches (equivalent to {1,})

? 0 or 1 match (equivalent to {0,1})

{n} Specific number of matches

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

{n,m} Range of matches (m not to exceed 255)

------------------------------------------------------

-----------------------------

Metacharacter Description

-----------------------------

^ Start of text 

$ End of text 

[[:<:]] Start of word 

[[:>:]] End of word

 

-----------------------------

 

示例

包含字符串1000

(jlive)[crashcourse]>SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

+--------------+

| prod_name    |

+--------------+

| JetPack 1000 |

+--------------+

1 row in set (0.00 sec)

包含字符串，000前有一个任意字符

(jlive)[crashcourse]>SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;

+--------------+

| prod_name    |

+--------------+

| JetPack 1000 |

| JetPack 2000 |

+--------------+

2 rows in set (0.00 sec)

包含字符串1000或2000

(jlive)[crashcourse]>SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

+--------------+

| prod_name    |

+--------------+

| JetPack 1000 |

| JetPack 2000 |

+--------------+

2 rows in set (0.00 sec)

匹配列表中的值和后面的字符串组合

(jlive)[crashcourse]>SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;

+--------------+

| prod_name    |

+--------------+

| .5 ton anvil |

| 1 ton anvil  |

| 2 ton anvil  |

+--------------+

3 rows in set (0.00 sec)

匹配非空

(jlive)[crashcourse]>SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name;

+----------------+

| vend_name      |

+----------------+

| ACME           |

| Anvils R Us    |

| Furball Inc.   |

| Jet Set        |

| Jouets Et Ours |

| LT Supplies    |

+----------------+

6 rows in set (0.00 sec)

匹配特殊字符.

(jlive)[crashcourse]>SELECT vend_name FROM vendors WHERE vend_name REGEXP '\.' ORDER BY vend_name;

+--------------+

| vend_name    |

+--------------+

| Furball Inc. |

+--------------+

1 row in set (0.00 sec)

(jlive)[crashcourse]>SELECT prod_name FROM products WHERE prod_name REGEXP '\([0-9] sticks?\)' ORDER BY prod_name;

+----------------+

| prod_name      |

+----------------+

| TNT (1 stick)  |

| TNT (5 sticks) |

+----------------+

2 rows in set (0.00 sec)

(jlive)[crashcourse]>SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\.]' ORDER BY prod_name;

+--------------+

| prod_name    |

+--------------+

| .5 ton anvil |

| 1 ton anvil  |

| 2 ton anvil  |

+--------------+

3 rows in set (0.00 sec)

(jlive)[crashcourse]>SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;

+--------------+

| prod_name    |

+--------------+

| JetPack 1000 |

| JetPack 2000 |

+--------------+

2 rows in set (0.01 sec)

查询结果与下面两条相同

SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9]{4}' ORDER BY prod_name;