在SQL中使用正则表达式
[*] 在字符串的开始处进行匹配：
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 '';
+--------------------------+
| 'abcdefh' regexp '' |
+--------------------------+
| 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 | bj@163.com |
| 11 | bsssj@163.com |
+------------+---------------+
2 rows in set (0.00 sec)
mysql> select first_name,email from customer where email regexp ".*@163[,.]com$";
+------------+---------------+
| first_name | email |
+------------+---------------+
| 11 | bj@163.com |
| 11 | bsssj@163.com |
+------------+---------------+
2 rows in set (0.00 sec)
mysql> select first_name,email from customer where email regexp ".*@163.com$";
+------------+---------------+
| first_name | email |
+------------+---------------+
| 11 | bj@163.com |
| 11 | bsssj@163.com |
+------------+---------------+
2 rows in set (0.00 sec)
mysql> select first_name,email from customer where email regexp "@163.com$";
+------------+---------------+
| first_name | email |
+------------+---------------+
| 11 | bj@163.com |
| 11 | bsssj@163.com |
+------------+---------------+
2 rows in set (0.00 sec)
mysql>
如果不实用正则表达式：
mysql>select first_name,email from customer where email like "%@163%.com" or email like "%@163%,com" ;
+------------+---------------+
| first_name | email |
+------------+---------------+
| 11 | bj@163.com |
| 11 | bsssj@163.com |
+------------+---------------+
2 rows in set (0.00 sec)
mysql>
页:
[1]