How to Use the PostgreSQL Like Operator

Introduction

In this ObjectRocket tutorial we will be discussing how to use the PostgreSQL LIKE operator. It’s primary function is to compare strings. For example if you wanted to query user emails that ended in @gmail.com. That’s something you could do with the LIKE operator. As you will see the LIKE operator is great to know because it is flexible, simple, and it’s a very common query performed in the real-world.

Prerequisites

Before we proceed on discussing how to use the like operator in PostgreSQL we need to make certain prerequisites.

• Make sure that there is a PostgreSQL server installed in your local device, execute the terminal command sudo systemctl status PostgreSQL that uses the systemd software suite in a Linux distribution system to view its status.

• You can also verify the version of the psql shell used for SQL queries in PostgreSQL by executing the command psql -V in the terminal.

Accessing the Terminal in Postgres

PostgreSQL also has an interactive terminal that is used for executing SQL queries or commands for the database.

To access the psql, execute the bash command :

Create a Database and Table

Before we proceed on the topic of this article, we must first create a database and a table for our like operator example in PostgreSQL.

To create a database, execute the command :

NOTE: The command \c is used to connect and navigate inside the PostgreSQL database and the command \l is used to get a list of database names in your local device.

And to create a table in PostgreSQL you must execute the command :

The PostgreSQL LIKE Operator

The PostgreSQL LIKE operator assigns text values patterns utilizing wildcards. Unless the statement query can be compared to the expression, it will return true.

Below is the following syntax for using a LIKE operator.

The two wildcards that represent the conjunction in a LIKE operator are :

• The percent sign % that represents as zero or multiple characters or numbers that are used for sequence matching of characters.

• The underscore sign _ that represents a single character or number that is used for matching the single character.

PostgreSQL Like Query Example

Supposedly we have a table with a value like this :

And we want to display names that contain the string ine. We can use the LIKE operator to do this query.

To execute :

The output returns the following result:

NOTE: We used the asterisk sign * to return all of the columns from the some_user table.

Here’s another example of a query to search for entries in the some_user table where the dob field contains the string 199. We use the % to match any string.

The result of this query is:

This will return rows that have 199 in any position.

Conclusion

We hope this tutorial on the LIKE operator in PostgreSQL was helpful to you. The use of this operator is very common so it is a good operator to become very familiar with. Thank you for joining us for this ObjectRocket tutorial.