PostgreSQL LIKE Operator: Query Data Using Pattern Matching

By /
Like Operator In Postgresql

In this tutorial, we will be learning about the LIKE operator which is a pattern-matching operator used to fetch data based on a specified pattern. We will see multiple patterns available to use with the LIKE operator and implement them in real-world examples. So, let’s get started!

Introduction to PostgreSQL LIKE Operator

The LIKE operator is a pattern-matching operator provided by PostgreSQL and is available in almost all relational database management systems like MySQL, SQL Server, PostgreSQL, etc. It is used with the WHERE clause to form a condition.

When you want to match the value with a particular pattern, you can use the LIKE operator. Note that, there is a predefined set of patterns that you can use with the LIKE operator. If you want to match the value with a very specific pattern, you will need to write a user-defined function in Perl or tcl.

Some of the use cases of the LIKE operator are- finding the names that start/end with a particular letter or word, values that contain a particular letter or word etc.

Note that, the LIKE operator in PostgreSQL is used for case-sensitive matches. If you want to match the value irrespective of the case, use the ILIKE operator.

There are two wildcards you can use with the LIKE operator for pattern matching as explained below:

  • % – The percent sign matches a sequence of zero or more characters.
  • _ – The underscore sign matches the single character.

Let’s see the syntax of using the LIKE operator now.

Syntax of PostgreSQL LIKE Operator

The syntax of using the LIKE operator in PostgreSQL is given below.

Syntax:

string LIKE pattern;
string NOT LIKE pattern;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the pattern contains the targeted letter or string along with the wildcards. A pattern can contain zero or more wildcard characters. Let’s see some examples.

SELECT 'mysql' LIKE '%m%',  -- true
'mysql' LIKE '_y%',    -- true
'mysql' LIKE 'my%',   -- true
'mysql' LIKE 'sql';       -- falseCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Patterns Examples
Patterns Examples

Here,

  • The first pattern matches with the string and checks if there are zero or more characters present before or after the character m.
  • The second pattern checks if there is exactly one character before the letter y and zero or more characters after y.
  • The third pattern checks if the string starts with ‘my’ and has zero or more letters after it.
  • The fourth pattern doesn’t contain any wildcards. Therefore, the operator will check if the string exactly matches the pattern.

Examples of PostgreSQL LIKE Operator

We will need a table first with some data in it to demonstrate the examples.

Here, we have already created a table called ’employees’ as shown below.

Employees Table Description
Employees Table Description
Employees Table Data
Employees Table Data

Perfect! Let’s now move forward.

Example 1: Multiple LIKE Operator Examples

Let’s now write queries using the LIKE operator to find:

  1. employees with names starting with the letter ‘J’
  2. employees having the letter ‘a’ in their name
  3. employees having the ‘a’ as a second letter in their name
  4. employees having a name ending with the letter ‘a’
SELECT * FROM employees
WHERE first_name LIKE 'J%';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Name Starting With J
Name Starting With J
SELECT * FROM employees
WHERE first_name LIKE '%a%';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Names Having Letter A
Names Having Letter A
SELECT * FROM employees
WHERE first_name LIKE '_a%';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Names Having Second Letter 'a'
Names Having Second Letter ‘a’
SELECT * FROM employees
WHERE first_name LIKE '%a';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Names Ending With 'a'
Names Ending With ‘a’

Example 2: ILIKE Operator

Now, let’s say the employee names start with either lowercase or capital case. Using the LIKE operator will return no rows if the letter case is different. In this case, you can use the ILIKE operator. Let’s see an example below.

SELECT * FROM employees
WHERE first_name LIKE '%j%';

SELECT * FROM employees
WHERE first_name ILIKE '%j%';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the first query uses the LIKE operator and the second query uses the ILIKE operator. In our table, there are a few names present that start with the letter ‘J’. However, all these names start with the capital letters. Let’s see the output of both queries.

Output:

ILIKE Operator Example
ILIKE Operator Example

As you can see, the first query returns nothing, whereas the second query returns the expected output.

Example 3: NOT LIKE Operator

The NOT LIKE operator simply returns the contrasting output. For example, if a certain row returns true for the LIKE operator, it will return false for the NOT LIKE operator and vice versa.

Let’s see an example where we will fetch all those employees whose names don’t start with the letter ‘J’.

SELECT * FROM employees
WHERE first_name NOT LIKE 'J%';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOT LIKE Example
NOT LIKE Example

As you can see, we have got all the names that don’t start with the letter ‘J’.

Conclusion

In this tutorial, we have learned the LIKE operator along with its extension ILIKE operator which is provided by PostgreSQL. The LIKE operator is one of the most used operators which comes into the picture when you want to search names, addresses, or any other text-based data. Play around with the multiple combinations of wildcard characters to create different patterns!

Reference

PostgreSQL official documentation on the pattern matching