SIMILAR TO Pattern
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.
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?
Press Run to execute the query once the engine is ready.
More Examples
Pattern with quantifiers
Find emails at gmail or yahoo domains.
Press Run to execute the query once the engine is ready.
Character class pattern
Find grades that are a single uppercase letter.
Press Run to execute the query once the engine is ready.