在SQL中使用Regex

標籤：在sql中使用Regex

1. 在字串的開始處進行匹配：

mysql> select ‘wqh‘ regexp ‘^w‘;

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

| ‘wqh‘ regexp ‘^w‘ |

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

|                 1 |

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

1 row in set (0.00 sec)

mysql> 

2.在字串的末尾處進行匹配：

mysql> select ‘wqh‘ regexp ‘h$‘;

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

| ‘wqh‘ regexp ‘h$‘ |

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

|                 1 |

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

1 row in set (0.00 sec)

mysql> 

3.匹配任意單個字元，包括分行符號：

mysql> select ‘abcd‘ regexp ‘.c‘, ‘abcd‘ regexp ‘.f‘;    

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

| ‘abcd‘ regexp ‘.c‘ | ‘abcd‘ regexp ‘.f‘ |

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

|                  1 |                  0 |

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

1 row in set (0.00 sec)

mysql> 

4.匹配括弧內的任一字元：

mysql> select ‘abcdefh‘ regexp ‘[fhk]‘;

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

| ‘abcdefh‘ regexp ‘[fhk]‘ |

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

|                        1 |

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

1 row in set (0.00 sec)

mysql>

執行個體：

mysql> select first_name,email from customer where email regexp "@163[,.]com$";  

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

| first_name | email         |

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

| 11         | [email protected]    |

| 11         | [email protected] |

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

2 rows in set (0.00 sec)

mysql> select first_name,email from customer where email regexp ".*@163[,.]com$"; 

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

| first_name | email         |

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

| 11         | [email protected]    |

| 11         | [email protected] |

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

2 rows in set (0.00 sec)

mysql> select first_name,email from customer where email regexp ".*@163.com$";   

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

| first_name | email         |

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

| 11         | [email protected]    |

| 11         | [email protected] |

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

2 rows in set (0.00 sec)

mysql> select first_name,email from customer where email regexp "@163.com$";  

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

| first_name | email         |

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

| 11         | [email protected]    |

| 11         | [email protected] |

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

2 rows in set (0.00 sec)

mysql> 

如果不實用Regex：

mysql>  select first_name,email from customer where email like "%@163%.com" or email like "%@163%,com" ;

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

| first_name | email         |

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

| 11         | [email protected]    |

| 11         | [email protected] |

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

2 rows in set (0.00 sec)

mysql> 

在SQL中使用Regex