[sql] PostgreSQL wildcard LIKE for any of a list of words

I have a simple list of ~25 words. I have a varchar field in PostgreSQL, let's say that list is ['foo', 'bar', 'baz']. I want to find any row in my table that has any of those words. This will work, but I'd like something more elegant.

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

One 'elegant' solution would be to use full text search: http://www.postgresql.org/docs/9.0/interactive/textsearch.html. Then you would use full text search queries.

Actually there is an operator for that in PostgreSQL:

SELECT *
FROM table
WHERE lower(value) ~~ ANY('{%foo%,%bar%,%baz%}');

All currently supported versions (9.5 and up) allow pattern matching in addition to LIKE.

Reference: https://www.postgresql.org/docs/current/functions-matching.html

PostgreSQL also supports full POSIX regular expressions:

select * from table where value ~* 'foo|bar|baz';

The ~* is for a case insensitive match, ~ is case sensitive.

Another option is to use ANY:

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

You can use ANY with any operator that yields a boolean. I suspect that the regex options would be quicker but ANY is a useful tool to have in your toolbox.