Pattern Matching (LIKE, ILIKE, SIMILAR TO, Regex) in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
What is Pattern Matching in SQL?
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.
How do you match text case-insensitively in PostgreSQL?
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:
SELECT name, email FROM customers WHERE email ILIKE '%.com'
ILIKE '%.com' matches [email protected], [email protected], and anything in between. Use it when casing in your data is inconsistent.
How do you use a regex in a PostgreSQL WHERE clause?
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.
What is SIMILAR TO in PostgreSQL?
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.
Why is LIKE with a leading wildcard slow?
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?
Practice Pattern Matching in SQL
Helix Systems' CRM team is pulling a list of customer-facing roles — anyone whose title mentions an Account function.
Write a query to return the ID, name, and title of every employee whose title contains the word Account, regardless of capitalization.
Assumptions:
- The
employeestable has one row per employee with anid, aname, and atitle. - A qualifying employee has a
titlethat containsaccountsomewhere in the string, with case ignored.
Output:
- One row per qualifying employee, with columns
id,name, andtitle.
Schema · hr4 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 Pattern Matching 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.
Start learning to practice all 9 Pattern Matching problems, with instant grading and mastery tracking.
Common questions about Pattern Matching
Are the regex operators case sensitive?
The plain one is, and there is a second form that is not. Matching upper case text against a lower case pattern fails with the case-sensitive operator and succeeds with the case-insensitive one, which is the same split as LIKE and ILIKE.
Does a regex match the whole string or any part of it?
Any part, which is the opposite of LIKE. A pattern with no anchors finds its text anywhere in the value, so add a caret at the start or a dollar at the end when you mean to pin it to one end, or both to require an exact match.
Can you use regex syntax inside a LIKE pattern?
No. LIKE understands only its own two wildcards, so a character class written in a LIKE pattern is matched literally and quietly finds nothing. Switch to one of the regex operators when the pattern needs more than any-sequence and any-single-character.
