5.21. PostgreSQL LIKE clause

In the PostgreSQL database, if we want to get data that contains certain characters, we can use the LIKE Clause.

In LIKE Clauses are usually used in conjunction with wildcards, which represent any character. In PostgreSQL, there are two main types of wildcards:

Percent sign%
Underline _

If you do not use the above two wildcards LIKE The result is the same for the clause and the equal sign.

5.21.1. Grammar ¶

The following is the use of the LIKE Clause collocation percent sign % And underline _ General syntax for getting data from a database:

SELECT FROM table_name WHERE column LIKE 'XXXX%';
或者
SELECT FROM table_name WHERE column LIKE '%XXXX%';
或者
SELECT FROM table_name WHERE column LIKE 'XXXX_';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX_';

You can do it in the WHERE Any condition is specified in the.

You can use it. AND Or OR Specify one or more conditions.

XXXX It can be any number or character.

5.21.2. Example ¶

The following is a demonstration of% and in the LIKE statement _ Some of the differences:

Example	Description
WHERE SALARY::text LIKE ‘200%’	Find the data that starts with 200 in the SALARY field.
WHERE SALARY::text LIKE’% 200%’	Find the data that contains 200 characters in the SALARY field.
WHERE SALARY::text LIKE’_ 00%’	Find the data that has 00 in the second and third positions in the SALARY field.
WHERE SALARY::text LIKE’2%’	Find the data in the SALARY field that starts with 2 and is longer than 3.
WHERE SALARY::text LIKE’% 2’	Find the data that ends in 2 in the SALARY field
WHERE SALARY::text LIKE’_ 2% 3’	Find the data in the SALARY field where 2 is in the second position and ends with 3
WHERE SALARY::text LIKE’2 million dollars 3’	Find the data in the SALARY field that begins with 2, ends with 3 and is 5 digits.

In PostgreSQL LIKE Clauses can only be used to compare characters, so in the above example, we want to convert an integer data type to a string data type.

Create the COMPANY table ( 下载 COMPANY SQL 文件 ), the data are as follows:

runoobdb# 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)

The following example will find the data whose AGE starts with 2:

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

The following results are obtained:

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

The following example will find out address The field contains - Data for characters:

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

The results are as follows:

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