PostgreSQL LIKE operator (original) (raw)

Last Updated : 12 Jul, 2025

In **PostgreSQL, the **LIKE operator is an essential tool for **pattern matching in SQL queries. Whether we're dealing with large datasets or searching for specific **string patterns, this operator provides a powerful way to filter and retrieve data based on partial matches. By Using **wildcard search techniques, users can match strings with a **high degree of **flexibility.

In this article, we will explain how the **LIKE operator works, including common **use cases and **examples, along with tips for optimizing our search queries.

What is PostgreSQL LIKE operator?

The LIKE operator in **PostgreSQL allows for **string matching within columns by using a pattern. It is commonly used in the **WHERE clause of a **SQL query to filter records based on whether the value in a column matches a given pattern. This operator is ideal for performing searches where we don't know the exact value but know part of the string or its structure.

**Syntax

string LIKE pattern;

**Key Terms

• **column_name: The column in which the search will be conducted.
• **pattern: The string pattern you're searching for, which can include **wildcards.

PostgreSQL Wildcards Used with LIKE

It is important to know that PostgreSQL provides with 2 special **wildcard characters for the purpose of pattern matching.

• **Percent ( %) for matching any sequence of characters.
• **Underscore ( _) for matching any single character.

Examples of PostgreSQL LIKE operator

In this section, we will go through multiple **PostgreSQL LIKE examples to show how we can use the **LIKE operator with **wildcards to match different **search patterns. we will be using the **sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.

Example 1: Finding Names That Start with a Specific Letter

Here we will make a query to find the customer in the "**customer" table by looking at the "**first_name" column to see if there is any value that begins with "K" using the **LIKE operator in our sample database.

**Query:

SELECT
first_name,
last_name
FROM
customer
WHERE
first_name LIKE 'K%';

**Output

**Explanation:

Notice few things in the above example, the WHERE clause contains a special expression: the **first_name, the **LIKE operator, and a string that contains a **percent (%) character, which is referred to as a pattern.

Example 2: Matching Patterns with a Single Character

Here we will query for **customers whose first name begins with any **single character, is followed by the literal string "**her", and ends with any number of characters using the **LIKE operator in our **sample database.

**Query:

SELECT
first_name,
last_name
FROM
customer
WHERE
first_name LIKE '_her%';

**Output

PostgreSQL LIKE operator Example2

**Explanation:

The pattern _her% ensures that the first character can be anything, followed by '**her', and any characters after it. This is an example of how to use **PostgreSQL string matching with wildcards to capture **flexible patterns.

**Example 3: Searching for Specific Characters in the Middle of a Name

We can also use the **_ wildcard to find names that match a certain structure. For example, let’s find all **customers whose first names contain the string '**er'.

**Query:

SELECT
first_name,
last_name
FROM
customer
WHERE
first_name LIKE '%er%';

**Output

**Explanation:

The %er% pattern matches any first name containing ****'er**', no matter where it appears.

**PostgreSQL NOT LIKE Operator

While the **LIKE operator is used for matching patterns, the NOT LIKE operator can be used when we want to exclude records that match a certain pattern. This is useful when filtering out data that doesn’t fit specific criteria.

Example 4

SELECT
first_name,
last_name
FROM
customer
WHERE
first_name NOT LIKE 'Jen%';

**Output

**Explanation:

This query returns all customers whose first names do not start with '**Jen'.

**PostgreSQL ILIKE: Case-Insensitive Pattern Matching

In some cases, you may want to perform a **case-insensitive search. **PostgreSQL offers the **ILIKE operator for this purpose, which functions similarly to the LIKE operator but ignores case differences.

Example 5:

SELECT
first_name,
last_name
FROM
customer
WHERE
first_name ILIKE 'bar%';

**Output

**Explanation:

The **ILIKE 'bar%' pattern matches any string starting with '**bar', '**Bar', '**BAR', etc., ignoring case.

**Using the ESCAPE Clause with LIKE Operator

Sometimes, our data might include the ****%** or **_ characters that should be treated as regular characters instead of wildcards. In such cases, you can use the **ESCAPE clause to specify an escape character.

Example 6:

SELECT
message
FROM
t
WHERE
message LIKE '%10$%%' ESCAPE '$';

**Output:

**Explanation:

In the pattern**%10$%%** , the first and last % are wildcards, but the $ character before % tells PostgreSQL to treat % as a literal character.

**Conclusion

In conclusion, the **PostgreSQL LIKE operator is an essential tool for **string matching in SQL queries, enabling users to find records that match specific search patterns. Whether we're using wildcards like% for **multiple characters or _ for a **single character, PostgreSQL's **pattern matching features offer great flexibility.

By applying **PostgreSQL LIKE examples in our queries, we can efficiently filter and retrieve data based on **partial or **complex string matches. This makes it a powerful option for querying large datasets where exact matches are not possible or practical.