Multiple Inverse Match

Question

Thanks for reading this. I hope you can help me. When I have a mysql table with these row values

ID | Search
========
1 | butterflies
2 | America
3 | birds of america
4 | American butterflies

how can I determine which rows have all the words in the search column occurring in the butterfly of america row, regardless of the number or order of search words.

(I would like to get 1,2 and 4 in this example) Now I use an encoded loop to solve this problem, it would be nice to fix it with mysql. I tried full-text search and regular expressions, but got completely stuck. Tx.

Answer 1

Nested replaces, no subqueries.

SELECT id, search FROM a WHERE LENGTH( TRIM( REPLACE( REPLACE( REPLACE( CONCAT( ' ', search, ' ' ) , ' butterflies ', ' ' ) , ' of ', ' ' ) , ' america ', ' ' ) ) ) = 0 id search 1 butterflies 2 america 4 america butterflies 

I have added spaces in the search words so that you do not match the middle of the words (for example, “from” to “coffee”). In addition, I added spatial letters to the search phrase to account for the first and last words.

Answer 2

 SELECT * FROM table_name WHERE search LIKE '%butterflies%' AND search LIKE '%of%' AND search LIKE '%america%'; 

or

 SELECT * FROM table_name WHERE search REGEXP 'butterflies|of|america'; // not working 

If I didn’t miss something :)

Edit: I'm missing something :(

Answer 3

Here is one of the methods I've experimented with (although not very effective):

 select search, replace(filtered, 'butterflies', '') as filtered from ( select search, replace(filtered, 'of', '') as filtered from ( select search, replace(search, 'america', '') as filtered from table_name a ) b ) c; 

This query will give you something like the following:

 +---------------------+----------+ | search | filtered | +---------------------+----------+ | butterflies | | | america | | | birds of america | birds | | america butterflies | | +---------------------+----------+ 

The last part of this work gave me some problem, though ... you need a where clause that will return all lines that are "empty" (that is, contain only whitespace characters).

This will filter out the third row and return the desired result. However, I could not get this to work using trim (), and I don't know why.

For example, I tried:

 where length(trim(c.filtered)) = 0; 

This did not give me the result set that I wanted. I no longer have time to study this right now, but I wanted to mention this approach if someone else wants to call back and complete the puzzle. If not, I will try to study this a bit later today or tomorrow.