%CASE-sensitive%

Bona Lee
Bona Lee
Jul 23, 2017 · 2 min read

Imagine this.

You are a Junior SQL developer at a company that mainly deals with online retail. Your company uses a PostgreSQL database that will hold a lot of information about each of your customers, products, and each customer’s purchase history.

Your manager comes by and gives you a list of people they would like for you to find. They want you to find all of the people that have ever purchased an item produced by a specific company made from polystyrene, which can lead to be a potential human carcinogenic. The company wanted to recall a few of their products that fit within this criteria and it is your job to notify those consumers who have gone home with a recalled product as soon as possible. So you attempt to do the following search:

SELECT * 
FROM Orders
INNER JOIN Products ON Orders.product_id = Products.id
WHERE Products.material LIKE "polystyrene";

However, it returns no customers because it is looking for “polystyrene” exactly, all lowercase. You must account for all products that have polystyrene anywhere in the list of materials. Sometimes it might be all capitalized, sometimes it will be all lowercase, other times you will have just the first letter be capitalized, “etc.”


In order to account for all of those instances, you write the following line of code:

SELECT *
FROM Orders
INNER JOIN Products ON Orders.product_id = Products.id
WHERE Product.material ILIKE "%polystyrene%";

“ILIKE” helps look for any products that has polystyrene within the material list while the “%” helps looks for all instances of the word whether each individual letter is capitalized or not.


And it quickly returns a list of 375 people who have purchased water bottles and tumblers made from polystyrene. Good job! Now time to go warn them about the recall!

Bona Lee

Written by

Bona Lee

A trilingual web-developer, world-traveller, fitness guru, and pineapple enthusiast

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade