MySQL check how many times a word appears inside a table cell

Question

I want to write a query to select from a table all rows with the word "piggy" in a column called Description.

SELECT * FROM table WHERE ...?

Thank you!

Answer 1

select * from table where description like '%piggy%'

will select and return all rows where the word piggy is part of the value of the column. If you want to count how many rows then:

select count(*) from table where description like '%piggy%'

Answer 2

select * from table where description like '%piggy%'

Answer 3

In MySQL, % is a wild-card. You don't use wild-cards with the = operator but with the LIKE operator.

SELECT * FROM table WHERE `Description` LIKE "%piggy%"

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

Answer 4

As mentioned several times you need a LIKE query for this. I would only warn that this is going to be terribly slow in case of a InnoDB table as it doesn't support fulltext scans. If you want better performance with a LIKE, then you should use MyISAM.

Anyway, if you want to implement a search engine, better look for existing API's. I don't know what programming language you're using, but if it was Java, I'd recommend Apache Lucene.