reverse match of multiple words

Question

Thanks for reading this. 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| america butterflies

how can I tell which rows have all the words in column 'search' occuring in the string "butterflies of america", regardless of the number or order of the search words.

(I would like to retrieve 1,2 and 4 in this example) I now use a coded loop to solve this problem, would be nice to fix it more efficient with mysql. I tried full text search and regular expressions but are complety stuck. Tx.

Answer 1

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 am not missing something :)

Edit: I was missing something :(

Answer 2

Here is one method that I experimented with (although not very efficient):

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 piece to make this work was giving me some trouble, though... you need a where clause which will return all rows that are "empty" (i.e. contain only whitespace characters).

That will filter out the third row and return the result set you desire. However, I wasn't able to 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 I wanted. I don't have anymore time to look into this right now, but I wanted to mention this approach in case someone else wants to chime in and finish solving the puzzle. If not, I will try to look into this a little more later today or tomorrow.