How to Use SIMILAR TO in PostgreSQL for Pattern Matching

In PostgreSQL, the SIMILAR TO operator is used to match strings against SQL-standard regular expressions. It works like LIKE but supports more complex patterns using regex-like syntax enclosed in ''. Use it in a WHERE clause to filter rows based on pattern matching.

Syntax

The basic syntax of SIMILAR TO is:

• expression SIMILAR TO pattern: Returns true if the expression matches the pattern.
• expression: The string or column to test.
• pattern: A string pattern using SQL regular expression syntax.

Patterns use % for any sequence of characters and _ for a single character, similar to LIKE, but also support regex groups and alternation with |.

sql

expression SIMILAR TO 'pattern'

Example

This example shows how to select names that start with 'Jo' and end with either 'hn' or 'seph' using SIMILAR TO:

sql

CREATE TEMP TABLE people(name TEXT);

INSERT INTO people VALUES
('John'), ('Joseph'), ('Joanna'), ('Mike');

SELECT name FROM people
WHERE name SIMILAR TO 'Jo(hn|seph)';

Output

 name 
------
 John 
 Joseph
(2 rows)

Common Pitfalls

Common mistakes when using SIMILAR TO include:

• Confusing it with LIKE: SIMILAR TO uses regex-style patterns, not simple wildcards.
• Forgetting to escape special characters in patterns.
• Using SIMILAR TO without quotes around the pattern string.

Example of wrong and right usage:

sql

-- Wrong: missing quotes around pattern
SELECT 'abc' SIMILAR TO abc;

-- Right: pattern in quotes
SELECT 'abc' SIMILAR TO 'a(c|b)c';

Output

ERROR:  syntax error at or near "abc"

 ?column? 
----------
 t
(1 row)

Quick Reference

Pattern Symbol	Meaning
%	Matches any sequence of characters
_	Matches any single character
(a|b)	Matches either 'a' or 'b'
[abc]	Matches any one character 'a', 'b', or 'c'
{n,m}	Matches between n and m repetitions of the previous element

Key Takeaways

Use SIMILAR TO for SQL-standard regex pattern matching in PostgreSQL.

Patterns must be enclosed in single quotes and support alternation with | and grouping with ().

Remember SIMILAR TO is more powerful than LIKE but requires correct regex syntax.

Always test your patterns to avoid syntax errors or unexpected matches.