reverse match of multiple words

Thanks for reading this. Hope you can help me.

When I have a Mysql table with these row value开发者_运维百科s

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.

---

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 added bookending spaces to the words to search for to ensure you don't match against the middle of words (e.g. 'of' in 'coffee'). Moreover, I added space bookends to the search phrase to account for the first and last words.

---

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.

---

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 :(