“LIKE” vs “ILIKE” operator in PostgreSQL

In this article, we will walk through the “LIKE” and “ILIKE” operators used in PostgreSQL.

PostgreSQL is a case-sensitive language in order to make it a case-insensitive language during pattern matching we make use of the ILIKE operator.

ILIKE operator works the same way as a LIKE operator but makes the language case-insensitive. Both the operators are used for pattern matching in PostgreSQL.

Pattern matching operators

1) LIKE (~~)

Syntax: WHERE value LIKE ‘xyz%’

2) NOT LIKE (!~~)

Syntax: WHERE value NOT LIKE ‘xyz%’

3) ILIKE (~~*)

Syntax: WHERE value ILIKE ‘xyz%’

4) NOT ILIKE (!~~*)

Syntax: WHERE value NOT ILIKE ‘xyz%’

In order to understand pattern matching operators let consider problem statements.

Consider a table called contacts having columns domainname, metatitle, metadescription, email, emaildescription.

SELECT * FROM CONTACTS;

Problem statement1: LIKE or (~~)

Write an SQL query to fetch all the details from the products table where the email starts with ‘admin’.

SELECT domainname,metatitle,metadescription,email,emaildescription FROM contacts WHERE email LIKE ‘admin%’;

Using Symbol(~~)

SELECT domainname,metatitle,metadescription,email,emaildescription FROM contacts WHERE email ~~ ‘admin%’;

Result:- Problem statement1

Problem statement2: NOT LIKE or (!~~)

Write an SQL query to fetch all the details from the products table where the email does not start with ‘admin’.

SELECT domainname,metatitle,metadescription,email,emaildescription FROM contacts WHERE email NOT LIKE ‘admin%’;

Using Symbol(!~~)

SELECT domainname,metatitle,metadescription,email,emaildescription FROM contacts WHERE email !~~ ‘admin%’;

Result:- Problem statement2

Problem statement3: ILIKE (~~*)

Write an SQL query to fetch all the details from the products table where the metatitle starts with ‘h’ and has ‘|’.

Let’s first try to implement this problem statement using ‘LIKE’

SELECT domainname,metatitle,metadescription,email,emaildescription FROM contacts WHERE metatitle LIKE ‘h%|%’.

Using the “LIKE” operator

So, there is no match found where the metatitle starts with ‘h”.

Now let’s make use of ILIKE making the search pattern matches case-insensitive in PostgreSQL.

SELECT domainname,metatitle,metadescription,email,emaildescription FROM contacts WHERE metatitle ILIKE ‘h%|%’;

Using Symbol(~~*)

SELECT domainname,metatitle,metadescription,email,emaildescription FROM contacts WHERE metatitle ~~* ‘h%|%’;

Result:- Problem statement3

Problem statement4: NOT ILIKE (~~*)

Write an SQL query to fetch all the details from the products table where the metatitle does not start with ‘h’ or ‘H’

SELECT domainname,metatitle,metadescription,email,emaildescription FROM contacts WHERE metatitle NOT ILIKE ‘h%’;

Using Symbol(!~~*)

SELECT domainname,metatitle,metadescription,email,emaildescription FROM contacts WHERE metatitle !~~* ‘h%’;

Result:- Problem statement3

Link for the data used in this article contacts.csv file: https://github.com/Nidhig631/Medium_article_data

Thanks for the read. Do clap if find useful🙂.