This approach will work both as a direct SQL query and as provided as a User Defined Function available in MySQL as of version 5.0. The approach is quite common in the statistical world, but as I was recently asked how to accomplish this, probably not so by rest.

Well, here we go, the following will give you the difference in character between the full string and the string with the word you want to count removed. This, divided by the length of the word you want to count will give you the number of occurenced will give you the number of times the word was there originally.

The foundamental statistics behind the result is generic and will work in any situation.

A very simple approach once you know it.

In MySQL 5.0 you can do:

CREATE FUNCTION wordcount
(
a VARCHAR(255),
b VARCHAR(255)
)
RETURNS INTEGER
CONTAINS SQL DETERMINISTIC
RETURN (CHAR_LENGTH(a)-CHAR_LENGTH(REPLACE(a, b, '')))/CHAR_LENGTH(b);

mysql> SELECT wordcount('test word word','word') AS mycount;
+---------+
| mycount |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)

Why?
"test word word" is 14 characters
14-6 = 8
8 / 4 = 2

hence two occurences