Hey everyone, I’m trying to figure out how to search my database table for entries that have certain words in a specific column. The tricky part is that I need to find these words regardless of their order. For example, if I’m looking for ‘apple’, ‘banana’, and ‘cherry’, I want my query to return results that have these words in any combination like ‘banana cherry apple’ or ‘cherry apple banana’.
I’m thinking it might look something like this:
SELECT * FROM fruits WHERE description HAS_ALL ('apple', 'banana', 'cherry')
But I’m not sure if that’s the right approach or if it even exists. Does anyone know how to do this in SQL? I’d really appreciate any help or suggestions. Thanks!
ooh, that’s an interesting challenge! have you considered using LIKE with wildcards? something like ‘%apple%’ AND ‘%banana%’ AND ‘%cherry%’? might be a bit slow for big databases tho. what about full-text search? some dbms have cool features for this kinda thing. what database are you using btw?
For this type of query, I’d recommend considering a full-text search solution if your database supports it. Many modern DBMS offer robust full-text search capabilities that can handle word order independence efficiently. Alternatively, you could create a function that splits the description into words, sorts them, and then compares against a sorted list of search terms. This approach would be more performant than using multiple LIKE clauses or complex regular expressions, especially on larger datasets. It’s worth noting that the exact implementation would depend on your specific database system and performance requirements.
yo, have u tried using REGEXP? it’s pretty sweet for this kinda stuff. u could do smth like WHERE description REGEXP ‘apple.*banana.*cherry|apple.*cherry.*banana|banana.*apple.*cherry|banana.*cherry.*apple|cherry.*apple.*banana|cherry.*banana.*apple’. might need tweaking but should work!