Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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%')
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.
                Interestingly, while both of these methods are more elegant than @chmullig 's solution (so +1), when checking 3 options at least, they execute significantly slower on large tables (91.5 million records in my case). I was seeing a time increase of about 2x when using either of these. Any idea why that might be?
– sage88
                Sep 7, 2017 at 5:07
                @sage88 I don't know off the top of my head but Erwin Brandstetter might and adding trigram indexes might help.
– mu is too short
                Sep 7, 2017 at 6:18
                How do you know it ? most of documentation I've read says that regex are slower and a LIKE %...
– DestyNova
                Jul 24, 2015 at 14:26
                According to dba.stackexchange.com/a/10696/27757 SIMILAR TO is internally translated to a regex search
– Mark K Cowan
                Sep 16, 2016 at 10:17
                I think using lower() is ineffective because it will first convert each string to lowercase, which is more costly than only a case-insensitive match
– gilad905
                Sep 20, 2019 at 10:57
                if you are using jpa then same native query can be written as below  @Query(value = "select * from table_name where  lower(< filter col name >) similar to lower(CONCAT('%', :string, '%')) ", nativeQuery = true) 	List<Object[]> test2(@Param("string") String string);  Here string = (foo|bar|baz)
– Abu Talha Siddiqi
                Feb 28 at 11:01
                ~~ is just another name for like: "The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. All of these operators are PostgreSQL-specific.". And '{%foo%,%bar%,%baz%}' is the text form of array['%foo%', '%bar%', '%baz%'].
– mu is too short
                Sep 6, 2018 at 17:19
                So can ilike be used with any & array in the same way? This looks clean if there is no need for fancy regex. Or is it going to be translated to regex internally anyway?
– mlt
                Feb 9, 2019 at 2:20
                @mlt That is a good question, reading the doc does not provide explicit answer. SIMILAR TO does convert into Regular Expression, ~ operator stands for POSIX Regular Expression, but this is not clear for LIKE.
– jlandercy
                Feb 19, 2019 at 11:47
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.