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.
You can use Postgres' SIMILAR TO operator which supports alternations, i.e.
select * from table where lower(value) similar to '%(foo|bar|baz)%'; 
SIMILAR TO is internally translated to a regex search
lower() is ineffective because it will first convert each string to lowercase, which is more costly than only a case-insensitive match
@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)Actually there is an operator for that in PostgreSQL:
SELECT * FROM table WHERE lower(value) ~~ ANY('{%foo%,%bar%,%baz%}'); 
~~ 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%'].
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.
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
