Yogesh Chauhan's Blog

LIKE and ILIKE Operators in Postgres

in Postgres on May 15, 2020

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.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to CREATE TABLE in SQL with and without using Another Table?SQL/MySQLHow to create a simple drop down menu using JavaScript and CSS?CSSAlternate Style Sheets in CSSCSSUseful (and probably ignored) HTML tags: Part 1HTMLHow to add a Pie Chart in Angular App?AngularWhen you don’t want to @forward every member in SCSS (Sass)SCSS