CODEWITHSUNDEEP
mysqltextcpu-word
Gal
Gal

Reputation: 23662

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

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!

Upvotes: 0

Views: 1049

Answers (4)

BalusC
BalusC

Reputation: 1108612

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.

Upvotes: 1

nash
nash

Reputation: 2181

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

Upvotes: 1

Apreche
Apreche

Reputation: 32879

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

Upvotes: 1

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103135

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%'

Upvotes: 1

Related Questions