SQL ILIKE

Postgres's case-insensitive version of LIKE — same wildcards, no case sensitivity.

What & Why

ILIKE is a Postgres-specific extension that works exactly like LIKE, except it ignores letter case entirely.

See How It Works

BUSINESS QUESTION

Match the campaign name 'Spring Launch' even when the search text uses uppercase letters.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

EXAMPLE QUERY

-- plain LIKE is case-sensitive and does not match
SELECT name FROM marketing.campaigns WHERE name LIKE 'SPRING LAUNCH';

-- ILIKE ignores letter case and matches the row
SELECT name FROM marketing.campaigns WHERE name ILIKE 'SPRING LAUNCH';

RESULT — ILIKE version

name
Spring Launch

ILIKE matches the stored mixed-case campaign name.

Now You Try

Practice this concept

Product wants features whose name or description mentions SQL, regardless of capitalization.

Available schema

product

Prefix tables with product.table_name.

namedescriptionteam

product.features

Column	Type
id	integer
name	text
description	text
team	text
released_at	timestamp with time zone
deprecated_at	timestamp with time zone
internal_priority	integer

query.sql

Beginner business practice