MySQL.REGEXP

浏览数：28 / 时间：2015年06月12日

SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP ‘1000‘
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+

SELECT prod_name FROM products WHERE prod_name REGEXP ‘.000‘ ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000|2000‘ ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

SELECT prod_name 
    -> FROM products
    -> WHERE prod_name REGEXP ‘[123] Ton‘
    -> ORDER BY prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+

SELECT prod_name FROM products WHERE prod_name REGEXP ‘1|2|3 Ton‘ ORDER BY prod_name;
+---------------+
| prod_name     |
+---------------+
| 1 ton anvil   |
| 2 ton anvil   |
| JetPack 1000  |
| JetPack 2000  |
| TNT (1 stick) |
+---------------+

SELECT prod_name FROM products WHERE prod_name REGEXP ‘[^123] Ton‘ ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
+--------------+

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  |
+--------------+

SELECT prod_name FROM products WHERE prod_name REGEXP ‘[a-z]‘ ORDER BY prod_name;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Bird seed      |
| Carrots        |
| Detonator      |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

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    |
| RAME           |
+----------------+

SELECT vend_name
    -> FROM vendors
    -> WHERE vend_name REGEXP ‘\\.‘
    -> ORDER BY vend_name;
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+


SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP ‘\\([0-9] sticks?\\)‘
    -> ORDER BY prod_name;
+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+


SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP ‘[[:digit:]]{4}‘
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

SELECT prod_name FROM products WHERE prod_name REGEXP ‘[0-9][0-9][0-9][0-9]‘ ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

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  |
+--------------+

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    |
| JetPack 1000   |
| JetPack 2000   |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

SELECT ‘hello‘ REGEXP ‘[0-9]‘;
+------------------------+
| ‘hello‘ REGEXP ‘[0-9]‘ |
+------------------------+
|                      0 |
+------------------------+