MySQL: Column Contains Word From List of Words

Question

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?

Solution 1

EDIT:

Something like this:

SELECT * FROM yourtable WHERE content LIKE '%apple%' OR content LIKE '%orange%'

You can loop your words to create WHERE clause conditions.

For Example:

$words = array( 'apple', 'orange' );
$whereClause = '';
foreach( $words as $word) {
   $whereClause .= ' content LIKE "%' . $word . '%" OR';
}

// Remove last 'OR'
$whereClause = substr($whereClause, 0, -2);

$sql = 'SELECT * FROM yourtable WHERE' . $whereClause;

echo $sql;

Output:

SELECT * FROM yourtable WHERE content LIKE "%apple%" OR content LIKE "%orange%" 

Solution 2

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;

Comments

[cbrandolino]

uh, I was apparently late and yours is more complete, so I guess you'll get a +1.

[mellowsoon]

This is what I'm doing now, and I was hoping to avoid it. While it does avoid the network overhead of separate queries, I would assume that MySQL is internally running running each word over every row in the table, which is essentially several queries. I was hoping MySQL had an optimized function for this type of query.

[Cedric Simon]

Thanks a lot. Exactly what I need.

[John]

Elegant and this should be the correct answer. I'd guess 5.0 is an acceptable minimal version

[Amin Fazlali]

using REGEXP 'Apple.*Orange.*Pear' will yield the results that contain all the words in the specified order.

[user2060451]

This does not bring all the results between the simple quotes to me. And it is by a far cry.

[Augusto Cesar de Camargo]

You made my night, tks.