返回顶部

收藏

mysql正则表达式使用示例

更多
/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)

mysql> /*
mysql>  Prior to MySQL Version 3.23.4, REGEXP is case sensitive,
mysql>  match either lowercase or uppercase 'b', use this query instead:
mysql> */
mysql> SELECT * FROM Bird WHERE name REGEXP '^[bB]';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

*/  
Drop table Bird;

CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);

INSERT INTO  Bird VALUES ('BlueBird','Joe','Car','f','1999-03-30',NULL);
INSERT INTO  Bird VALUES ('RedBird','Yin','Bus','m','1979-04-30',1998-01-30);
INSERT INTO  Bird VALUES ('RedBird','Yin','Bus','m','1998-01-30',NULL);

select * from Bird;
/*
 Prior to MySQL Version 3.23.4, REGEXP is case sensitive, 
 match either lowercase or uppercase 'b', use this query instead:
*/
SELECT * FROM Bird WHERE name REGEXP '^[bB]';

标签:SQL,MySQL,正则表达式

收藏

0人收藏

支持

0

反对

0

相关聚客文章
  1. OurMySQL 发表 2013-09-02 13:26:10 查看mysql实时运行sql的工具–orztop
  2. zhu329599788@126 发表 2015-07-22 08:15:29 Mysql跨表更新 多表update sql语句总结
  3. zhuangli 发表 2017-07-12 20:38:28 MySQL句柄恢复的简单尝试
  4. lixiphp 发表 2013-06-06 10:55:58 解决MySQL SQL语句出现#1093错误
  5. 新一 发表 2013-06-27 00:11:46 mysql自联结
  6. musiXboy 发表 2013-09-30 06:36:47 Google App Engine For PHP Preview 初体验
  7. metaboy 发表 2013-11-06 07:45:23 关于子查询和连接的趣味小题目
  8. caishzh 发表 2014-11-10 01:30:32 SQL表连接图解
  9. xiaoli.he 发表 2017-03-23 14:30:22 MySQL 5.6, 5.7并行复制测试(二)(r12笔记第10天)
  10. sulong 发表 2009-06-08 08:49:54 mysql用sum()等函数后始终返回一行
  11. 博主 发表 2015-06-23 15:16:18 Mysql的SQL模式
  12. ITJesse 发表 2014-01-05 20:06:56 将SQL Server中的数据导入MySQL