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?

user330315 · Accepted Answer · 2019-07-09 14:40:55Z

4

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

answered Jul 9, 2019 at 14:40

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

lmat - Reinstate Monica Over a year ago

This only works for whole-word matches. Apparentl it's good enough for the original poster, but is there anything for general substring matches?

Collectives™ on Stack Overflow

postgres: Search for multiple words in string

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related