How to use SQL LIKE condition with multiple values in PostgreSQL?

Playing with SIMILAR TO

As the complexity increases, PostgreSQL offers SIMILAR TO to search multiple patterns more efficiently. This tactic is especially beneficial with a common prefix having multiple possible suffixes:

SELECT * FROM table WHERE col SIMILAR TO '(val1|val2|val3)%'; -- Keep your options open, folks! 😉


Regex for complex pattern champ

For more complex pattern matching, PostgreSQL provides ~ or ~* operators that enable regex pattern matching:

SELECT * FROM table WHERE col ~ '^(val1|val2|val3)'; -- Flex those regular expression skills 💪


If you prefer to keep things casual and case-insensitive, lean on ~*:

SELECT * FROM table WHERE col ~* '^(val1|val2|val3)'; -- Lowercase? Uppercase? No problem!


Through regex, your queries can become compact, short, and efficient, replacing multiple LIKE or OR conditions.

Array gymnastics with ANY

The ANY keyword paired with arrays is a wonderful combo that enhances comparison logic:

SELECT * FROM table WHERE col LIKE ANY (ARRAY['val1%', 'val2%', 'val3%']); -- Because variety is the spice of SQL arrays 😄


By pitting col against each value within the array, it functions similarly to multiple OR conditions but boasts a more concise syntax.

Creating pattern sets with VALUES

Enhance your pattern matching skills using the VALUES keyword, which is great for building sets applicable in joins or comparisons:

SELECT * FROM table
JOIN (VALUES ('val1'), ('val2'), ('val3')) AS pattern(value) ON col LIKE pattern.value; -- Joining the party with VALUES 🎉


This method is beneficial for pre-defining patterns and improves readability when dealing with numerous potential matches.

Aim for wide SQL applicability

SIMILAR TO offers a generic solution that might be adaptable to various SQL databases. It's prudent to tailor your queries to your current database version (like PostgreSQL 9.1+) while striving for broad SQL applicability.

Minimizing redundancy

Efficient querying is synonymous with minimizing redundancy. Regular expressions and PostgreSQL's LIKE, SIMILAR TO, ANY, and ALL features offer a plethora of ways to give your pattern matching a streamlined makeover.