postgres: Search for multiple words in string

Question

I'm implementing a rudimentary form of search for my company backoffice system. I would like to find all products names that contain all words in a search query.

So if I have these two products:

• Deodorant with cucumber flavor
• Deoderant with apple flavor

the search query: cucumber deoderant should match only Deoderant with cucumber flavor.

I can make it work for the query deoderant cucumber like so:

SELECT product_name FROM products WHERE name ~* regexp_replace('deoderant cucumber', '\s+', '.*', 'g');

but this approach does not work when the order is not the name in the query and the product name.

I know that this is possible by doing something like

SELECT product_name FROM products WHERE name ~* 'deoderant' AND name ~* cucumber';

but I would ideally like to stay away from string interpolation as it becomes a bit messy in my current environment.

Is there another, more elegant way to do this?

Answer 1

You could convert the value into an array and then use the "contains" operator @> with the words you are looking for:

SELECT product_name 
FROM products 
WHERE regexp_split_to_array(lower(name), '\s+') @> array['deodorant', 'cucumber'];

Online example: https://rextester.com/GNL7374