PostgreSQL wildcard LIKE for any of a list of words

PostgreSQL Wildcard LIKE: Finding Rows with Any of a List of Words

So you have a PostgreSQL database with a table that has a varchar field, and you want to find any rows that contain any of the words from a given list. You've already tried using multiple like conditions, but you're looking for a more elegant solution. Well, you're in luck! In this blog post, we'll explore a few easy solutions to this problem, so let's get started. 💪

The Not-So-Elegant Solution

Before we dive into the more elegant solutions, let's quickly recap the not-so-elegant approach you've already tried:

select *
from table
where (lower(value) like '%foo%' or lower(value) like '%bar%' or lower(value) like '%baz%')

While this approach gets the job done, it can quickly become cumbersome and repetitive, especially if you have a long list of words. Plus, it's not the most efficient solution either.

Solution 1: Using the ANY Operator with an Array

One of the more elegant solutions to this problem involves using the ANY operator along with an array. Here's how you can rewrite the query using this approach:

select *
from table
where lower(value) like any (array['%foo%', '%bar%', '%baz%'])

By using the ANY operator in this way, you can provide a list of patterns as an array and check if any of them match the value column. This approach is not only more concise but also improves the query's performance by avoiding repeated like conditions.

Solution 2: Regular Expressions with the ~ Operator

Another way to tackle this problem is by using regular expressions. PostgreSQL offers powerful regex capabilities, making it a great option for pattern matching. Here's how you can rewrite the query using the ~ operator:

select *
from table
where lower(value) ~ 'foo|bar|baz'

The ~ operator allows you to match a column against a regular expression pattern. In this case, we're using the pipe (|) character to signify an OR operation, matching any row that contains either "foo," "bar," or "baz." This approach can be especially useful if you need more advanced pattern matching options.

Take It to the Next Level with Indexing

Now that you have some elegant solutions to your original problem, let's take it a step further! To optimize the query's performance, consider adding an index on the value column. This will significantly speed up the search operation, particularly if you have a large table.

create index value_idx on table (lower(value));

By creating an index on the lowercase version of the value column, you can dramatically improve the search speed for your wildcard matching queries.

Conclusion

There you have it! Finding rows in PostgreSQL that contain any of the words from a list can be elegantly achieved using the ANY operator with an array or regular expressions with the ~ operator. Don't forget to take advantage of indexing to further boost the query performance. 🚀

Now it's your turn. Have you come across any other creative solutions for this problem? Let us know in the comments below! And if you found this post helpful, don't forget to share it with your fellow PostgreSQL enthusiasts. Happy querying! 😊