LIKE and ILIKE Operators in Postgres

Postgres LIKE and ILIKE operators are used in pattern matching.

LIKE

We have seen LIKE examples in my previous blog posts. But let's explore in details this time.

Syntax:


WHERE value LIKE 'abc%';

LIKE Query:

I am using the database for all examples. It is available on my Github public repo


SELECT product_name, unit_price 
FROM products 
WHERE product_name LIKE 'Ma%';

//Output
product_name, unit_price
"Mascarpone Fabioli"	32
"Maxilaku"	20
"Manjimup Dried Apples"	53

The query will list all the products with name starting with 'Ma'.

How did the Postgres find out that we wanted name that starts from 'Ma'?

We used '%' to search for a specified pattern in a column.

There are two wild cards often used in conjunction with the LIKE operator:

% – The percent sign represents zero, one, or multiple characters

_ – The underscore represents a single character

What is we add % in the front and back of 'Ma' in above query?


SELECT product_name, unit_price 
FROM products 
WHERE product_name LIKE '%Ma%';

//Output
product_name      unit_price 
"Queso Manchego La Pastora"    38
"Sir Rodney's Marmalade"    81
"Nord-Ost Matjeshering"    25.89
"Mascarpone Fabioli"    32
"Gula Malacca"    19.45
"Maxilaku"    20
"Manjimup Dried Apples"    53

Then it's going to find out all the products which has 'Ma' (Capital M and small a) somewhere in their name. It doesn't matter where, maybe in start, or end or in the middle.

How does the underscore work in the query?

The _ (underscore) will only look for one character unlike %, which looks for unlimited letters after the specified word or letter.

So, 'Ma%' means any name that starts with 'Ma' — no matter how long the name will be.

But 'Ma_' means only look for 3 letters in the name that starts with 'Ma'.

I hope my explanation makes sense. Let me know in comments.

LIKE with _ (underscore) Query


SELECT product_name, unit_price 
FROM products 
WHERE product_name LIKE 'Cha_';

//Output
product_name    unit_price 
"Chai"	           18

There are products with the name 'Cha' in the database table but it will only display Chai because of the condition.

Also, we can just add NOT in the query above and it will give all the alternative results.

NOT LIKE with _ (underscore) Query


SELECT product_name, unit_price 
FROM products 
WHERE product_name NOT LIKE 'Cha_';

//Output
product_name, unit_price 
"Chang"	19
"Aniseed Syrup"	10
"Chef Anton's Cajun Seasoning"	22
....
....

As we can see there is a product name with 'Cha' but it has more than 3 letters in it. That's why the alternative result set has that product.

ILIKE

ILIKE is noting but LIKE with case-insensitive. You can have lowercase search and it will display all the rows matching–lower case, upper case and capitalized.

ILIKE with % (percentage) Query


SELECT product_name, unit_price 
FROM products 
WHERE product_name ILIKE 'cha%';

//Output
product_name, unit_price 
"Chai"	18
"Chang"	19
"Chartreuse verte"	18

We can use symbols as well instead of LIKE, NOT LIKE, ILIKE or NOT ILIKE.

ILIKE with % (percentage) Query using Symbol (~~*)


SELECT product_name, unit_price 
FROM products 
WHERE product_name ~~* 'cha%';

//Output
product_name, unit_price 
"Chai"    18
"Chang"    19
"Chartreuse verte"    18

We can use

~~ symbol for LIKE,

!~~ symbol for NOT LIKE,

~~* symbol for ILIKE and

!~~* symbol for NOT ILIKE.

« Previous Next »