PostgreSQL LIKE Clause

In PostgreSQL, if we need to retrieve data that contains certain characters, we can use the LIKE clause.

In the LIKE clause, it is commonly used in conjunction with wildcards, which represent any character. In PostgreSQL, there are mainly two wildcards:

Percent sign %
Underscore _

If the above wildcards are not used, the LIKE clause yields the same result as the equal sign =.

Syntax

The following is the general syntax for retrieving data from the database using the LIKE clause with the percent sign % and underscore _:

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_';

You can specify any condition in the WHERE clause.

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

XXXX can be any number or character.

Examples

Below are examples demonstrating the differences between % and _ in LIKE statements:

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

In PostgreSQL, the LIKE clause is only used for character comparison, so in the above examples, we need to convert integer data types to string data types.

Create the COMPANY table (download the COMPANY SQL file here) with the following data:

tutorialprodb# 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 where AGE starts with 2:

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

The result will be:

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 where the address field contains the '-' character:

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

The result is as follows:

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

❮ Home Postgresql Syntax ❯