postgres regex query to match multiple strings pattern

Question

A postgres query selects the elements which have a name column containing any strings from an array:

select "elements".* from "elements" where "elements"."name" ~* 'hap|bir'

This works well.

Now, what is the regex pattern to select the elements only if the column contains every strings of the array (in no particular order)?

Answer 1

Your query produces the same result as the following non-regex LIKE expression using an array

SELECT * 
  FROM elements 
WHERE lower(name) LIKE ANY(ARRAY['%hap%', '%bir%']);

So, to select from elements only if the column contains every strings of the array, you change it from ANY to ALL

SELECT * 
  FROM elements 
WHERE lower(name) LIKE ALL(ARRAY['%hap%', '%bir%']);

Demo

Answer 2

Guess what, you can actually combine the regexp match operator, with ALL over an array.

SELECT * 
  FROM elements 
WHERE name ~* ALL(ARRAY['\mhap\M', '\mbir\M'])

This may be particularly advantageous if your search text is not a single field, but a concatenation of several fields.

(Note: \m is start of word, \M is end of word.)

Answer 3

Here is a regex pattern to select the elements only if the column contains every strings of the array (in no particular order):

select "elements".* from "elements" where "elements"."name" ~* '(?=.*?(hap))(?=.*?(bir))'

from here.