提要:你将学习到如何使用 PostgreSQL 的 LIKE 和 ILIKE 运算符来通过模式匹配查询数据。

PostgreSQL LIKE 运算符简介

假设店长要求你找到一个顾客,他完全不记得这个名字。他只是记得客户的名字好像由Jen开头。你该如何找到店长要找的确切客户?你可以通过一个个查看 customer 表中的 first_name 字段里,是否有 Jen 开头的数据。这是一个很乏味的工作,因为 customer 表中有很多数据。

幸运的是,你可以使用 PostgreSQL 的 LIKE运算符完成这个任务,如下所示:

SELECT
 first_name,
        last_name
FROM
 customer
WHERE
 first_name LIKE 'Jen%';

查找Jen开头的名字

注意,WHERE 子句里有一个特殊的表达式:first_name 字段、LIKE 运算符和一个包含百分号(%)的字符串。这就是所谓的模式(pattern)

这个查询将返回所有 first_name 字段以 Jen 开头,后面跟着任意字符序列的数据。这种技术称为模式匹配,更通俗地说,这叫模糊查询

通过将字符串与通配符(wildcard characters)的结合来构造模式,并使用 LIKENOT LIKE 运算符来查找匹配。PostgreSQL 提供了两个通配符:

  • 百分号(%)用于匹配任何字符序列。
  • 下划线(_)用于匹配任意单个字符。

LIKE 运算符的语法如下:

string LIKE pattern

如果 stringpattern 相匹配,这个表达式返回 true;否则,返回 false

可以将 NOT 运算符和 LIKE 运算符进行组合:

string NOT LIKE pattern

如果 LIKE 返回 false,则表达式返回 true,反之亦然。

如果模式中不包含任何通配符,那么 LIKE 的行为和 =(等于)运算符类似。

PostgreSQL 模式匹配示例

PostgreSQL LIKE 示例

看看下面的例子:

SELECT
 'foo' LIKE 'foo', -- true
 'foo' LIKE 'f%', -- true
 'foo' LIKE '_o_', -- true
 'bar' LIKE 'b_'; -- false

解释一下:

  • 第一个表达式返回 true,是因为 foo 模式不包含任何通配符,所以 LIKE 的行为和 = 类似。
  • 第二个表达式返回 true,是因为 f% 模式匹配所有以 f 开头,后面跟着任意数量字符的字符串。
  • 第三个表达式返回 true,是因为 _o_模式匹配以任何单个字符开头(foo里的f自然也匹配),后面跟着一个字母o,最后以任何单个字符结尾的字符串。
  • 第四个表达式返回 false,是因为 b_模式匹配以b开头,后面跟着任何单个字符的字符串,bar并不能和该模式相匹配。

可以在模式的开头和/或结尾使用通配符。比如,下面的例子将返回名字中包含 er 的客户—— Jenifer, Kimberly 等等。

SELECT
 first_name,
        last_name
FROM
 customer
WHERE
 first_name LIKE '%er%'

查找带er的名字

可以像下面的例子这样,组合使用百分号和下划线通配符:

SELECT
 first_name,
 last_name
FROM
 customer
WHERE
 first_name LIKE '_her%';

组合通配符

这个表达式将匹配所有单个字符开头,后面跟着 her,然后以任意多个字符结束的名字。

PostgreSQL NOT LIKE 示例

下面的例子返回所有名字不是以 Jen 开头的客户:

SELECT
 first_name,
 last_name
FROM
 customer
WHERE
 first_name NOT LIKE 'Jen%';

not like

注意,我们在 WHERE 子句里使用了 NOT LIKE 运算符。

PostgreSQL 对 LIKE 的扩展

PostgreSQL 提供了和 LIKE 行为相似的 ILIKE 运算符。不同的是 ILIKE 在进行模式匹配时,会忽略大小写。即是说,它是以不区分大小写的方式进行模式匹配的。如下例所示:

SELECT
 first_name,
 last_name
FROM
 customer
WHERE
 first_name ILIKE 'BAR%';

ilike

BAR% 模式匹配所有以 BARBarBaR 等开头的字符串。如果把本例中的 ILIKE 换成 LIKE,那么将没有任何数据返回。

PostgreSQL 还提供了一些和 LIKENOT LIKE 以及 NOT ILIKE 功能一样的运算符:

  • ~~ 运算符和 LIKE 等效。
  • ~~* 运算符和 ILIKE 等效。
  • !~~ 运算符和 NOT LIKE 等效。
  • !~~* 运算符和 NOT ILIKE 等效。
上一篇:PostgreSQL的LIMIT
下一篇:PostgreSQL的IN