CODEWITHSUNDEEP
postgresql
rutchkiwi
rutchkiwi

Reputation: 576

postgres: Search for multiple words in string

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:

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?

Upvotes: 3

Views: 2792

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 4

Related Questions