Postgresql 简明教程

PostgreSQL - LIKE Clause

PostgreSQL LIKE 运算符用于使用通配符匹配文本值和模式。如果搜索表达式可以匹配模式表达式，LIKE 运算符将返回 true，即 1 。

The PostgreSQL LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1.

与 LIKE 运算符结合使用的有两个通配符 −

There are two wildcards used in conjunction with the LIKE operator −

The percent sign (%)
The underscore (_)

百分号表示零、一或多个数字或字符。下划线表示一个数字或字符。这些符号可以结合使用。

The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character. These symbols can be used in combinations.

如果这两个符号的任一个未与 LIKE 子句结合使用，则 LIKE 将充当等于运算符。

If either of these two signs is not used in conjunction with the LIKE clause, then the LIKE acts like the equals operator.

Syntax

% 和 _ 的基本语法如下：

The basic syntax of % and _ is as follows −

SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'

您可以使用 AND 或 OR 运算符组合 N 个条件。此处 XXXX 可以是任何数字或字符串值。

You can combine N number of conditions using AND or OR operators. Here XXXX could be any numeric or string value.

Example

以下是一些示例，显示了带有“%”和“_”运算符的不同的 LIKE 子句的 WHERE 部分 −

Here are number of examples showing WHERE part having different LIKE clause with '%' and '_' operators −

S. No.	Statement & Description
1	WHERE SALARY::text LIKE '200%' Finds any values that start with 200
2	WHERE SALARY::text LIKE '%200%' Finds any values that have 200 in any position
3	WHERE SALARY::text LIKE '_00%' Finds any values that have 00 in the second and third positions
4	WHERE SALARY::text LIKE '2_%_%' Finds any values that start with 2 and are at least 3 characters in length
5	WHERE SALARY::text LIKE '%2' Finds any values that end with 2
6	WHERE SALARY::text LIKE '_2%3' Finds any values that have 2 in the second position and end with a 3
7	WHERE SALARY::text LIKE '2___3' Finds any values in a five-digit number that start with 2 and end with 3

让我们举一个真实的例子，考虑表 COMPANY ，其记录如下 -

Let us take a real example, consider the table COMPANY, having records as follows −

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

下面是一个示例，它将显示 COMPANY 表中所有 AGE 以 2 开头的记录 -

The following is an example, which would display all the records from COMPANY table where AGE starts with 2 −

testdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';

这将产生以下结果 -

This would produce the following result −

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
(7 rows)

下面是一个示例，它将显示 COMPANY 表中所有在文本中带有连字符 (-) 的 ADDRESS 记录 -

The following is an example, which would display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text −

testdb=# SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

这将产生以下结果 -

This would produce the following result −

 id | name | age |                      address              | salary
----+------+-----+-------------------------------------------+--------
  4 | Mark |  25 | Rich-Mond                                 |  65000
  6 | Kim  |  22 | South-Hall                                |  45000
(2 rows)