Unit 09.01: Matching with LIKE and its limits

Unit ID: SQL-M09-U02 - Estimated active time: 14-17 minutes Objective: write a pattern match that finds every intended row regardless of how it was typed.

The search that finds a third of the answer

SELECT COUNT(*) FROM customers WHERE city LIKE '%delhi%';
-- 534

534 looks like a plausible answer. The real number is 1,604:

SELECT COUNT(*) FROM customers WHERE LOWER(city) LIKE '%delhi%';
-- 1604

LIKE is case-sensitive in DuckDB and PostgreSQL, so '%delhi%' matched only the lowercase spelling and missed both New Delhi variants. The query found one third of the customers it was asked for, and reported it with complete confidence.

Three ways to match case-insensitively

-- fragment: WHERE clauses shown on their own
-- 1. Normalise both sides (portable, explicit)
WHERE LOWER(city) LIKE '%delhi%'          -- 1604

-- 2. ILIKE (DuckDB, PostgreSQL - not standard SQL)
WHERE city ILIKE '%delhi%'                -- 1604

-- 3. Normalise once in a CTE, then match repeatedly
WITH c AS (SELECT LOWER(TRIM(city)) AS city FROM customers)
SELECT COUNT(*) FROM c WHERE city LIKE '%delhi%';

Option 1 is the safest default - it says what it does and runs anywhere. ILIKE is convenient and is another portability trap of the kind Module 2 and Module 7 flagged: absent from SQL Server and Oracle.

What the wildcards mean

% - any sequence of characters, including none
_ - exactly one character

'delhi%' matches only strings starting with delhi, so it would miss New Delhi entirely even after lowering. Leading % is what makes it a contains-match - and also what prevents an index from being used, which matters on large tables.

Where LIKE stops being the right tool

Pattern matching is for shapes, not meanings. LIKE cannot handle:

Spelling variants - Bengaluru and Bangalore share no useful pattern
Word boundaries - '%in%' matches India, Bengaluru, and printing
Structured extraction - pulling a domain out of an email is a job for SPLIT_PART or a regex

For a fixed set of known values, an explicit list is clearer and faster than a pattern:

-- fragment: WHERE clause shown on its own
WHERE LOWER(TRIM(city)) IN ('new delhi', 'mumbai')

Practice

Write a query counting customers in Mumbai that works regardless of case or trailing whitespace, then explain why LIKE '%mumbai%' alone would be both unreliable and imprecise.

Check your answer

SELECT COUNT(*) FROM customers WHERE LOWER(TRIM(city)) = 'mumbai';
-- 1070

LIKE '%mumbai%' is unreliable because it is case-sensitive, and imprecise because a contains-match would also catch a hypothetical Navi Mumbai or Mumbai Suburban - different places. Equality against a normalised value says exactly what is meant.

Takeaway

LIKE is case-sensitive here. Normalise both sides, and prefer an equality or IN test when you know the values you are looking for.

---