PostgreSQL Like With Examples

In this PostgreSQL tutorial, we will learn about “Postgresql like” which is about pattern matching and cover the following topics.

PostgreSQL like query
PostgreSQL like case insensitive
PostgreSQL like multiple columns
PostgreSQL like in
PostgreSQL like regex
PostgreSQL like multiple values
PostgreSQL like for integer
PostgreSQL like any

Table of Contents

PostgreSQL like query

In Postgresql, suppose we are a database developer of a company in the United States that sell online products. when we want to find some information like finding information about a specific product. But we don’t remember the full name of a product or we only know about the half name or some words about that product like product starting with “Ref”.

In that situation, we can use the LIKE operator to match some words with the product to find that product starting with the “Ref”.

Run the below query to know “how LIKE operator works” by finding the first name of employees starting with ‘Sh’.

SELECT first_name FROM employee
WHERE first_name like 'Sh%';

In the above code, the WHERE clause contains a special expression that is the first_name, the LIKE operator, and a string that contains a percentage sign ( % ). The string ‘Sh%’ is called a pattern.

The output of the above code is given below.

In the above output, it showed the which start with ‘Sh’ only.

Now, find the name of employees whose first name ends with ‘h’ using the below code.

SELECT first_name from employee
WHERE first_name like '%h';

Now, we are going to see another example with the table us_citites that contains the information about state name, population, density, etc of the United States. The description of the table is given below.

Let’s know the name of cities of the United States whose density is greater than 4000 using the below query and city name starts with ‘S’.

SELECT city FROM us_cities
WHERE densit > 4000 and city like 'S%';

Read Postgresql Delete Row

PostgreSQL like case insensitive

In Postgresql, like can’t match case insensitive or work alone. we need to use another function Called LOWER which converts any string values such as names in lower case.

We are going to perform the same query as we performed in the above sub-section.

SELECT first_name 
  FROM employee
 WHERE LOWER(first_name) like 'sh%';

In the above code, we are making these query case insensitive

The output of the above code is given below.

Read PostgreSQL Rename Column

PostgreSQL like multiple columns

The LIKE operator can also be used with multiple columns or more than one column.

Let’s find the first and last names of employees starting with ‘Sa’ and ending with ‘Ma’ using the below code.

SELECT first_name,last_name FROM employee
WHERE first_name like 'Sa%' AND last_name like 'Ma%';

In the above code, we are selecting the name of an employee using the first_name, last_name column from the employee table. Then specifying the condition that select the first name which starts with ‘Sa’ and last_name starts with ‘Ma’ using WHERE clause.

In the above output, the query returns results that show the name of employees whose first name, last_name start with ‘Sa’ and end with ‘Ma’ respectively.

Read PostgreSQL Trim with Examples

PostgreSQL like regex

In Postgresql, we can achieve the functionality of a LIKE operator using the regex (regular expression) operator, and that is (~).

Let’s find the name of the employee whose name starts with ‘Ja’ using the regular expression.

SELECT first_name FROM employee where first_name ~ 'Ja';

In the above code, we are selecting the frist_name from the employee table which starts with ‘Ja’, In the WHERE clause condition is specified using the regex operator (~) that acts like a LIKE operator.

The output of the above code is given below.

The output of the above code shows all first_name start with ‘Ja’.

Read PostgreSQL Length + 12 Examples

PostgreSQL like multiple values

In Postgresql, the like operator can also match the multiple values in a column.

Run the below code to find the first name of employees starting with ‘Sa’ and ‘Ma’.

SELECT first_name FROM employee
WHERE first_name like 'Sa%' or first_name like 'Ma%';

As usual, select the first_name column from the employee in the SELECT statement, then specify the condition using the WHERE clause that we need only the name of employees that start with ‘Sa’ or ‘Ma’ and both.

The output of the above code is given below.

In the above output, we can see the result of the query that all the names start with ‘Sa’ and ‘Ma’ only.

Read Postgresql now() function

PostgreSQL like for integer

In Postgresql, we can also find or match the integer using the LIKE operator.

Let’s find the name of employees whose birth year is 1957 using the below code.

SELECT first_name,birth_date FROM employee
WHERE CAST(birth_date AS TEXT) like '1957%';

In the above code, extracting the information about first name and date of birth of employees using SELECT statement. After that specifying the condition using WHERE clause that we need only those names whose birth year starting from 1957.

In the WHERE clause, first, we are casting the birth_date column to text using the CAST function because the LIKE operator doesn’t work on the date data type.

The output of the above code is given below.

The output shows the name of employees whose birth year is 1957.

Read Postgresql Average + Examples

PostgreSQL like any

In Postgresql, Any is an operator that compares a value to a set or list of values, so we can use any with the LIKE operator in Postgresql.

We will use the below code to find the name of any employee whose first name starts with ‘An’, ‘Sa’, and ‘Ma’.

SELECT first_name FROM employee WHERE first_name like any (array['An%', 'Sa%', 'Ma%']);

In the above code, we are selecting the first name of employees whose names start with ‘An’, ‘Sa’, and ‘Ma’.

In the WHERE clause, we are specifying conditions that we need any first name of employees using ANY operator that returns true if any value of the subquery meets the condition.

We have also used the array to create a set of values using the array[].

The output of the above code is given below.

In the above output, it shows any first names of employees that start with ‘An’, ‘Sa’, and ‘Ma’.