I have a list of words. Lets say they are 'Apple', 'Orange', and 'Pear'. I have rows in the database like this:
------------------------------------------------
|author_id | content |
------------------------------------------------
| 54 | I ate an apple for breakfast. |
| 63 | Going to the store. |
| 12 | Should I wear the orange shirt? |
------------------------------------------------
I'm looking for a query on an InnoDB table that will return the 1st and 3rd row, because the content
column contains one or more words from my list. I know I could query the table once for each word in my list, and use LIKE and the % wildcard character, but I'm wondering if there is a single query method for such a thing?
MySQL (I believe the 5.0 version) added the ability to use regular expressions in your SQL.
Check out: http://www.brainbell.com/tutorials/MySQL/Using_MySQL_Regular_Expressions.htm
SELECT author_id, content
FROM AuthorTableName
WHERE content REGEXP 'Apple|Orange|Pear'
ORDER BY author_id;