MariaDB 正则
----------------------------------------------------------------
Class
----------------------------------------------------------------
[:alnum:]
[:alpha:]
[:blank:]
[:cntrl:]
[:digit:]
[:graph:]
[:lower:]
[:print:]
[:punct:]
[:space:]
[:upper:]
[: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;