Chinaunix首页 | 论坛 | 博客
  • 博客访问: 961971
  • 博文数量: 83
  • 博客积分: 32
  • 博客等级: 民兵
  • 技术积分: 2080
  • 用 户 组: 普通用户
  • 注册时间: 2010-08-12 09:45
个人简介

努力学习,努力分享,努力.....在努力..努力离开..

文章分类

全部博文(83)

文章存档

2017年(3)

2015年(1)

2014年(47)

2013年(32)

分类: Mysql/postgreSQL

2013-12-17 17:14:23

正则表达式(Regular Expression),是指一个用来描述或者匹配一系列符合某个语句规则的字符串的单个字符.在MySQL中我们如何使用正则呢?
下面几个小实验例子说明一下:

"^"字符串开始匹配,返回结果为1表示匹配,返回0表示不匹配
mysql> select 'highkyz' regexp '^h';
+-----------------------+
| 'highkyz' regexp '^i' |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

"$"字符串末尾匹配
mysql> select 'highkyz' regexp 'z$';
+-----------------------+
| 'highkyz' regexp 'g$' |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

"."匹配任意单个字符,包括换行符.
mysql> select 'highkyz' regexp '.q','highkyz' regexp '.k';
+-----------------------+-----------------------+
| 'abcdefg' regexp '.q' | 'abcdefg' regexp '.z' |
+-----------------------+-----------------------+
|                     0 |                     1 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)

"[...]"匹配出括号内的换行符
mysql> select 'highkyz' regexp "[fhk]";
+--------------------------+
| 'highkyz' regexp "[fhk]" |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

"[^...]"匹配不出括号内的任意字符
mysql> select 'efg' regexp "[^XYZ]",'X' regexp "[^XYZ]";
+-----------------------+---------------------+
| 'efg' regexp "[^XYZ]" | 'X' regexp "[^XYZ]" |
+-----------------------+---------------------+
|                     1 |                   0 |
+-----------------------+---------------------+
1 row in set (0.00 sec)

mysql> create table t(name varchar(20),email varchar(40));
Query OK, 0 rows affected (0.44 sec)

mysql> insert into t values ('shanghai','shanghai@163.com');
Query OK, 1 row affected (0.03 sec)

mysql> insert into t values ('shanghai126','shanghai@126.com');  
Query OK, 1 row affected (0.03 sec)

mysql> insert into t values ('shanghai188','shanghai@188.com');    
Query OK, 1 row affected (0.02 sec)

mysql> select * from t;
+------------+-------------------+
| name       | email             |
+------------+-------------------+
| shanghai    | shanghai@163.com |
| shanghai126 | shanghai@126.com |
| shanghai188 | shanghai@188.com |
+------------+-------------------+
3 rows in set (0.00 sec)

mysql> select name,email from t where email regexp "@163[.,]com$";
+----------+-----------------+
| name     | email           |
+----------+-----------------+
| shanghai | shanghai@163.com|
+----------+-----------------+
1 row in set (0.00 sec)


MySQL REGEXP:http://dev.mysql.com/doc/refman/5.6/en/regexp.html#operator_regexp
阅读(20909) | 评论(0) | 转发(0) |
0

上一篇:如何清理MySQL BIN-LOG

下一篇:MySQL:EVENT

给主人留下些什么吧!~~