4.3.4.7 Pattern Matching

MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi, grep, and sed.

SQL pattern matching enables you to use “_” to match any single character and “%” to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. You do not use = or <> when you use SQL patterns; use the LIKE orNOT LIKE comparison operators instead.

To find names beginning with “b”:

mysql> SELECT * FROM pet WHERE name LIKE 'b%';+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

To find names ending with “fy”:

mysql> SELECT * FROM pet WHERE name LIKE '%fy';+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

To find names containing a “w”:

mysql> SELECT * FROM pet WHERE name LIKE '%w%';+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

To find names containing exactly five characters, use five instances of the “_” pattern character:

mysql> SELECT * FROM pet WHERE name LIKE '_____';+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use theREGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

The following list describes some characteristics of extended regular expressions:

  • .” matches any single character.
  • A character class “[...]” matches any character within the brackets. For example, “[abc]” matches “a”, “b”, or “c”. To name a range of characters, use a dash. “[a-z]” matches any letter, whereas “[0-9]” matches any digit.
  • *” matches zero or more instances of the thing preceding it. For example, “x*” matches any number of “x” characters, “[0-9]*” matches any number of digits, and “.*” matches any number of anything.
  • A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)
  • To anchor a pattern so that it must match the beginning or end of the value being tested, use “^” at the beginning or “$” at the end of the pattern.

To demonstrate how extended regular expressions work, the LIKE queries shown previously are rewritten here to use REGEXP.

To find names beginning with “b”, use “^” to match the beginning of the name:

mysql> SELECT * FROM pet WHERE name REGEXP '^b';+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

If you really want to force a REGEXP comparison to be case sensitive, use the BINARY keyword to make one of the strings a binary string. This query matches only lowercase “b” at the beginning of a name:

mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

To find names ending with “fy”, use “$” to match the end of the name:

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

To find names containing a “w”, use this query:

mysql> SELECT * FROM pet WHERE name REGEXP 'w';+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the previous query to put a wildcard on either side of the pattern to get it to match the entire value like it would be if you used an SQL pattern.

To find names containing exactly five characters, use “^” and “$” to match the beginning and end of the name, and five instances of “.” in between:

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

You could also write the previous query using the {n} (“repeat-n-times”) operator:

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

Section 13.5.2, “Regular Expressions”, provides more information about the syntax for regular expressions.

原文发布于微信公众号 - 数据库SQL(SQLdba)

原文发表时间:2016-06-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏小鹏的专栏

安装和使用NLTK分词和去停词

安装NLTK可能出现的问题: 1. pip install ntlk 2. 如果遇到缺少stopwords报错如下:(http://johnlaudun.org...

2197
来自专栏乐享123

How to Penetrate GFW With ShadowSocks Docker Container on Centos7

1584
来自专栏乐沙弥的世界

RAC 数据库的启动与关闭

        RAC数据库与单实例的差异主要表现在多个实例通过集群件来统一管理共享的资源。因此原有的单实例的管理方式,如数据库、监听器等的关闭启动等可以使用原...

662
来自专栏杂烩

canal安装

在mysql中 配置canal数据库管理用户,配置相应权限(repication权限)

772
来自专栏xiaoheike

mysql二进制文件操作语法(mysql binary log operate statements)

在 mysql 配置文件中配置 log-bin,重启 mysql my.cnf (on Linux/unix) or my.ini (on Windows) ...

843
来自专栏写代码的海盗

在Ubuntu安装go编译环境

在Ubuntu安装go编译环境 好记性不如烂笔头,所以趁热打铁记录下golang编译环境的安装过程。   首先下载一些依赖包: sudo apt-get in...

2424
来自专栏数据之美

详解 MySQL 5.7 新的权限与安全问题

1、新版 MySQL 权限问题:  问题:SQL Error (1130): Host '192.168.1.100' is not allowed to co...

95010
来自专栏Golang语言社区

Linux Golang 环境搭建(版本1.6.2)

1、下载安装包 https://storage.googleapis.com/golang/go1.6.2.linux-amd64.tar.gz 2、解压缩安装...

3395
来自专栏乐沙弥的世界

使用DBMS_REDEFINITION在线切换普通表到分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区...

822
来自专栏乐沙弥的世界

MySQL修改复制用户及密码

    在生产环境中有时候需要修改复制用户账户的密码,比如密码遗失,或者由于多个不同的复制用户想统一为单独一个复制账户。对于这些操作应尽可能慎重以避免操作不同导...

614

扫码关注云+社区