229

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%')
mu is too short
  • 426,620
  • 70
  • 833
  • 800
chmullig
  • 13,006
  • 5
  • 35
  • 52

5 Answers5

362

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.

Alter Lagos
  • 12,090
  • 1
  • 70
  • 92
mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 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 07 '17 at 05:07
  • @sage88 I don't know off the top of my head but Erwin Brandstetter might and adding [trigram indexes](https://stackoverflow.com/a/13452528/479863) might help. – mu is too short Sep 07 '17 at 06:18
223

You can use Postgres' SIMILAR TO operator which supports alternations, i.e. 

select * from table where lower(value) similar to '%(foo|bar|baz)%';
mu is too short
  • 426,620
  • 70
  • 833
  • 800
Nordic Mainframe
  • 28,058
  • 10
  • 66
  • 83
  • 1
    Regex might speed this up a bit: http://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql – approxiblue Jul 16 '13 at 15:07
  • How do you know it ? most of documentation I've read says that regex are slower and a LIKE %... – DestyNova Jul 24 '15 at 14:26
  • 7
    According to http://dba.stackexchange.com/a/10696/27757 `SIMILAR TO` is internally translated to a regex search – Mark K Cowan Sep 16 '16 at 10:17
  • 1
    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 '19 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 test2(@Param("string") String string);` Here string = (foo|bar|baz) – Abu Talha Siddiqi Feb 28 '23 at 11:01
31

Actually there is an operator for that in PostgreSQL:

SELECT *
FROM table
WHERE lower(value) ~~ ANY('{%foo%,%bar%,%baz%}');
jlandercy
  • 7,183
  • 1
  • 39
  • 57
  • 18
    `~~` 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."](https://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-LIKE). And `'{%foo%,%bar%,%baz%}'` is the text form of `array['%foo%', '%bar%', '%baz%']`. – mu is too short Sep 06 '18 at 17:19
  • 2
    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 09 '19 at 02:20
  • @mlt That is a good question, reading the [doc](https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-LIKE) 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 '19 at 11:47
1

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
Kalendae
  • 2,256
  • 1
  • 21
  • 23
0

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

Aaron Thomas
  • 5,054
  • 8
  • 43
  • 89