PostgreSQL Where Like Statement

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

The WHERE clause in PostgreSQL is used as a filter to search for records that fulfill a specified condition, with each condition having an operator. When one of the operators is LIKE, the LIKE-WHERE clause is used to search for a specified pattern using various wildcard symbols. The function of the WHERE clause is to match a pattern to the LIKE operator to return a value of “true.” This tutorial will explain how to use the PostgreSQL where like statement.

Prerequisites

  • PostgreSQL must be properly installed and configured on the local system in order to use the PostgreSQL where like function.

  • A basic knowledge of PostgreSQL is required in order to follow the examples in this tutorial.

PostgreSQL WHERE LIKE

The PostgreSQL WHERE clause is used for filtering records that match a pattern to the LIKE operator and executed as an expression that will return a “true” value.

Following is the basic syntax for the WHERE-LIKE function:

1
SELECT col1, col2 FROM tbl_name WHERE col1 LIKE 'expression';

Use the PostgreSQL like operator syntax

The following two wildcards are used with the LIKE operator:

  • % This symbol stands for zero, or a couple pf characters.

  • _ This symbols is used to represent a single character.

Some commonly used syntax

The following syntax searches for values that start with “a”:

1
WHERE column_name LIKE 'a%'

The following syntax looks for values that end with “a”:

1
WHERE column_name LIKE '%a'

The following syntax searches for values that have “a” in any position.

1
WHERE column_name LIKE '%a%'

The following syntax looks or for values that have “a” in second position.

1
WHERE column_name LIKE '_a%'

The following syntax searches for values that have “a” in second position and end with “b.”

1
WHERE column_name LIKE '_a%b'

The following syntax searches for values that begin with an “a” and end with “b.”

1
WHERE column_name LIKE 'a_b'

PostgreSQL Like Operator Examples

The following example uses the table named “Countries”:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
country_id | country_name | continent | code
------------+--------------------------+---------------+------
35 | South Africa | Africa | AF
14 | Canada | North America | NA
51 | Cuba | North America | NA
41 | Mexico | North America | NA
45 | United States OF America | North America | NA
85 | Argentina | South America | SA
37 | Venezuela | South America | SA
63 | Philippines | Asia | AS
83 | China | Asia | AS
22 | Russia | Asia | AS
21 | United Arab Emirates | Asia | AS
62 | Germany | Europe | EU
73 | Italy | Europe | EU
28 | United Kingdom | Europe | EU

The table will now be used to query the following WHERE-LIKE example:

1
SELECT * FROM countries WHERE country_name LIKE 'C%';

The table in the above example will display all the records where the country_name starts with the letter C.

The result of the query above should resemble the following:

1
2
3
4
5
country_id | country_name | continent | code
------------+--------------+---------------+------
14 | Canada | North America | NA
51 | Cuba | North America | NA
83 | China | Asia | AS

A second WHERE-LIKE example follows:

1
SELECT * FROM countries WHERE continent LIKE '%Am%';

The result of the above second query should resemble the following:

1
2
3
4
5
6
7
8
country_id | country_name | continent | code
------------+--------------------------+---------------+------
14 | Canada | North America | NA
51 | Cuba | North America | NA
41 | Mexico | North America | NA
45 | United States OF America | North America | NA
85 | Argentina | South America | SA
37 | Venezuela | South America | SA

The following query example displays all of the records where the second letter of the continent is ‘s’:

1
SELECT * FROM countries WHERE continent LIKE '_s%';

The result of the above query should resemble the following:

1
2
3
4
5
6
country_id | country_name | continent | code
------------+----------------------+-----------+------
63 | Philippines | Asia | AS
83 | China | Asia | AS
22 | Russia | Asia | AS
21 | United Arab Emirates | Asia | AS

Conclusion

This tutorial explained how to use the PostgreSQL where like statement for filtering records that match a pattern to the LIKE operator. The tutorial first explained the PostgreSQL WHERE-LIKE statement and how to use the PostgreSQL LIKE operator syntax. The article then covered some of the commonly used wildcard syntax and then provided a few PostgreSQL Like operator WHERE-LIKE examples. Remember that the function of the PostgreSQL WHERE clause is to match a pattern to the LIKE operator that will return a value of “true.”

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.