Pattern Matching (LIKE, ILIKE, SIMILAR TO, Regex) in SQL
PostgreSQL provides four pattern matching mechanisms for string columns: `LIKE`, `ILIKE`, `SIMILAR TO`, and the POSIX regex operators. Each uses a different pattern language with different expressive power and different performance characteristics.
You already know LIKE for pattern matching with % wildcards. ILIKE and the POSIX regex operators extend that idea — each handles more complex matching requirements.
You're filtering a customer table for email addresses from .com domains, or pulling product codes that follow a specific format like SKU-0042-B. LIKE '%.com' handles the first case. Validating a format with exactly four digits between two dashes needs something more expressive. That's what the regex operators are for.
ILIKE is the simplest extension — it's LIKE with case-insensitive matching. The pattern syntax is identical: % for any sequence of characters, _ for exactly one character. The only difference is that the match ignores case:
ILIKE '%.com' matches [email protected], [email protected], and anything in between. Use it when casing in your data is inconsistent.
For more complex patterns, PostgreSQL has POSIX regex operators: ~ (case-sensitive match), ~* (case-insensitive match), !~ (no match, case-sensitive), !~* (no match, case-insensitive). Unlike LIKE, these are not anchored by default — the pattern matches if it appears anywhere in the string. Use ^ to anchor at the start and $ at the end:
SELECT name, code
FROM products
WHERE code ~ '^SKU-[0-9]{4}-[A-Z]$'This matches codes that start with SKU-, followed by exactly four digits, a dash, and one uppercase letter. The regex syntax is the same dialect used in Python and most command-line tools, so it carries over directly.
SIMILAR TO exists as a middle ground between LIKE and full regex. It adds alternation (|), repetition (+, *), and grouping (()) to LIKE-style patterns. It also anchors the pattern at both ends by default, so SIMILAR TO 'hello' matches only the exact string 'hello', and you need SIMILAR TO '%hello%' to match it anywhere. In practice, most analysts skip SIMILAR TO. The POSIX operators already cover everything it can do with a more familiar syntax. You'll see it occasionally in existing code, and now you know what it is.
All four mechanisms propagate NULL: if the column value is NULL, the match expression returns NULL, which WHERE treats as false.
The one thing that trips people up: a leading % prevents index use.
LIKE 'prefix%' — a pattern anchored at the start — can use a B-tree index on the column. LIKE '%suffix' or LIKE '%anywhere%' cannot. PostgreSQL has to scan every row. The same applies to ILIKE and the regex operators. On large tables, where wildcards appear in your pattern directly affects query speed.
You want to find all emails containing 'gmail' anywhere, case-insensitive. Which is correct?
9 Pattern Matching (LIKE, ILIKE, SIMILAR TO, Regex) practice problems
Write a query to return the ID, name, and title of every employee whose title contains the word `Account`, regardless of capitalization.
Write a query to return the ID, name, and city of every customer whose city contains `new`, regardless of capitalization.
Write a query to return the ID and name of every product whose name contains `Pro`, `Plus`, or `Max` as written, with capitalization respected.
Write a query to return the ID and name of every product whose name contains at least one digit.
Write a query to return the ID, name, and email of every customer whose email address does not contain `gmail`, `yahoo`, or `hotmail`.
Write a query to return the ID and name of every employee whose title begins with `VP`.
Write a query to return the ID and event type of every event whose event type contains `click` or `view`, regardless of capitalization.
Write a query to return the ID and name of every employee whose title contains `Manager` or `Director` somewhere in the string, with capitalization respected.
Write a query to return the ID and name of every customer whose name fits the simple two-word form.
These problems are part of the Pattern Matching (LIKE, ILIKE, SIMILAR TO, Regex) lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.