PostgreSQL LIKE Clause

In the PostgreSQL database, if we want to retrieve data containing certain characters, we can useLIKEclause.

In the LIKE clause, it is usually used in combination with wildcards. Wildcards represent any characters. In PostgreSQL, there are mainly the following two wildcards:

  • percent sign%
  • underscore_

If the above two wildcards are not used, the LIKE clause and the equal sign (=)=give the same results.

Syntax

The following is the general syntax for using the LIKE clause with the percent sign%and underscore_to retrieve data from the 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 specify any conditions in the WHERE clause.

You can use AND or OR to specify one or more conditions.

XXXXCan be any number or character.

Examples

The following demonstrates in LIKE statements%and_some differences:

Example Description
WHERE SALARY::text LIKE '200%' Find data in the SALARY field that starts with 200.
WHERE SALARY::text LIKE '%200%' Find data in the SALARY field that contains the characters 200.
WHERE SALARY::text LIKE '_00%' Find data in the SALARY field that has 00 in the second and third positions.
WHERE SALARY::text LIKE '2_%_%' Find data in the SALARY field that starts with 2 and has character length greater than 3.
WHERE SALARY::text LIKE '%2' Find data in the SALARY field that ends with 2.
WHERE SALARY::text LIKE '_2%3' Find data in the SALARY field where 2 is in the second position and ends with 3.
WHERE SALARY::text LIKE '2___3' Find data in the SALARY field that starts with 2, ends with 3, and is 5 digits long.

In PostgreSQL, the LIKE clause can only be used for comparing characters. Therefore, in the above examples, we need to convert integer data types to string data types.

Create the COMPANY table (Download COMPANY SQL file), with the following data content:

exampledb# 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 data whose AGE starts with 2:

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

The following results are obtained:

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)

The following example will find data in the address field that contains-the character:

exampledb=# 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)
Other extensions