SIMILAR TO Pattern
intermediatefiltering
2 min read
What is SIMILAR TO?
SIMILAR TO is an SQL-standard pattern matching operator that blends LIKE wildcards (% and _) with regular expression features like alternation (|), grouping (()), and repetition (+, *, ?).
It sits between the simplicity of LIKE and the full power of POSIX regular expressions (~).
Syntax
SELECT * FROM table_name
WHERE column SIMILAR TO 'pattern';When to Use
- When LIKE is too simple but full regex is overkill
- Matching values against a set of alternatives
- Patterns that need repetition quantifiers
Key Points
- Anchored — SIMILAR TO matches the entire string (like LIKE), not a substring.
- Alternation — Use
|to match alternatives:'cat|dog'matches 'cat' or 'dog'. - Quantifiers —
+(one or more),*(zero or more),?(zero or one). - Character Classes — Use
[a-z]for ranges,[abc]for sets. - Wildcards —
%and_work as in LIKE.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SIMILAR TO Pattern Challenge
Write a query that find students with first name Alice or Bob.
Expected result
Rows where first_name is exactly Alice or Bob.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is SIMILAR TO?
Initializing database...Each run starts from fresh sample data.
More Examples
Pattern with quantifiers
Find emails at gmail or yahoo domains.
Initializing database...Each run starts from fresh sample data.
Character class pattern
Find grades that are a single uppercase letter.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is the difference between SIMILAR TO and LIKE?
SIMILAR TO adds regex features like alternation (|), quantifiers (+, *, ?), and character classes ([a-z]). LIKE only supports % and _ wildcards.
Should I use SIMILAR TO or POSIX regex (~)?
For simple patterns, SIMILAR TO is cleaner. For complex regex, use the ~ operator which supports full POSIX syntax.