PostgreSQL wildcard LIKE for any of a list of words

Question

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

User · Answer

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

User · Answer

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

User · Answer

Actually there is an operator for that in PostgreSQL   SELECT   FROM table WHERE lower value     ANY    foo   bar   baz

User · Answer

You can use Postgres  SIMILAR TO operator which supports alternations  i e    select   from table where lower value  similar to    foo bar baz

User · Answer

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

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

Examples related to sql

Examples related to postgresql