Count occurrences of a word in a row in MySQL

Question

I'm making a search function for my website, which finds relevant results from a database. I'm looking for a way to count occurrences of a word, but I need to ensure that there are word boundaries on both sides of the word ( so I don't end up with "triple" when I want "rip").

Does anyone have any ideas?

---

People have misunderstood my question:

How can I count the number of such occurences within a single row?

Answer 1

This is not the sort of thing that relational databases are very good at, unless you can use fulltext indexing, and you have already stated that you cannot, since you're using InnoDB. I'd suggest selecting your relevant rows and doing the word count in your application code.

Answer 2

You can try this perverted way:

SELECT 
(LENGTH(field) - LENGTH(REPLACE(field, 'word', ''))) / LENGTH('word') AS `count`
ORDER BY `count` DESC

• This query can be very slow
• It looks pretty ugly
• REPLACE() is case-sensitive