PostgreSQL Pattern Matching: LIKE VS Not LIKE VS ILIKE

In PostgreSQL, LIKE, NOT LIKE, and ILIKE operators are used along with the wildcards to perform the pattern matching. Two types of wildcards are used in Postgres to specify a pattern: a percentage sign, “%,” and an underscore sign, “_”. The percentage wildcard "%" matches sequences of characters, while the underscore "_" matches a single character.

This write-up will teach you the difference between LIKE, ILIKE, and NOT LIKE operators in PostgreSQL. So, let’s begin!

What is a LIKE Operator and How Does it Work?

The LIKE operator matches the search expression with the specified pattern and retrieves true if the match is found. The LIKE operator is case sensitive, which means the LIKE operator will consider “ABC” and “abc” two different strings and hence retrieves false in such a case. Use the below-specified syntax to perform text matching using the LIKE operator:

SELECT FROM tab_name
WHERE col_name LIKE pattern;

Use the wildcards to specify a pattern of your choice in place of a pattern.

Example 1: How Does the LIKE Operator Work?

We have created a table named “article_info”, whose data is shown in the following snippet:

SELECT * FROM article_info;
img

We will utilize the percent wildcard with the LIKE operator to find a substring “function” from the article_title column:

SELECT article_title 
FROM article_info
WHERE article_title LIKE '%Function%';
img

The output snippet proves that there are two titles in the “artile_info” table that contain a substring “Function”.

Example 2: Case-Sensitivity in LIKE Operator
The LIKE operator is case-sensitive, so it will retrieve false if the perfect match does not found:

SELECT article_title 
FROM article_info
WHERE article_title LIKE '%function%';
img

The above snippet proves that the LIKE operator is case-sensitive.

What is ILIKE Operator and How Does it Work in Postgres?

The ILIKE operator matches the search expression with the given pattern irrespective of the letter case. Use the below-specified syntax to perform text matching using the NOT LIKE operator:

SELECT FROM tab_name
WHERE col_name ILIKE pattern;

Example: How Does the ILIKE Operator Work?

Let’s implement the ILIKE operator on the same previous example and see how it works:

SELECT article_title 
FROM article_info
WHERE article_title ILIKE '%function%';
img

The output snippet proves that the “ILIKE” operator retrieves the data irrespective of the letter case.

What is a NOT LIKE Operator and How Does it Work?

The NOT LIKE operator negates the results of the LIKE operator, which means the NOT LIKE operator will retrieve false if the match is found and true if the match is found in the string. Use the below-specified syntax to perform text matching using the NOT LIKE operator:

SELECT FROM tab_name
WHERE column_name NOT LIKE pattern;

Use the wildcards to specify a pattern of your choice in place of a pattern.

Example: How Does the NOT LIKE Operator Work?

In this example, we will use the NOT LIKE operator instead of the LIKE operator to find all those strings that don’t have the “Function” substring in the article_title column:

SELECT article_title 
FROM article_info
WHERE article_title NOT LIKE '%Function%'
img

The output shows that the NOT LIKE operator retrieves all the strings other than those that contain a substring “Function”.

Conclusion

The LIKE operator matches the search expression with the specified pattern and retrieves true if the match is found. The NOT LIKE operator negates the results of the LIKE operator, which means the NOT LIKE operator will retrieve false if the match is found and true if the match is found in the string. The LIKE operator is case sensitive while the ILIKE operator matches the search expression with the given pattern irrespective of the letter case. This write-up explained how to use the LIKE, NOT LIKE, and ILIKE operators in Postgres to perform Pattern matching.