A simple 'User defined' MySQL function that you can use to count the number of words in a text string.

It's 'simple' because you don't need to memorise it. Just copy & paste it, and use it as you wish. See below for instructions. But first, the code:

DELIMITER $$
CREATE FUNCTION wordcount(str TEXT)
       RETURNS INT
       DETERMINISTIC
       SQL SECURITY INVOKER
       NO SQL
  BEGIN
    DECLARE wordCnt, idx, maxIdx INT DEFAULT 0;
    DECLARE currChar, prevChar BOOL DEFAULT 0;
    SET maxIdx=char_length(str);
    WHILE idx < maxIdx DO
        SET currChar=SUBSTRING(str, idx, 1) RLIKE '[[:alnum:]]';
        IF NOT prevChar AND currChar THEN
            SET wordCnt=wordCnt+1;
        END IF;
        SET prevChar=currChar;
        SET idx=idx+1;
    END WHILE;
    RETURN wordCnt;
  END
$$
DELIMITER ;

Don't forget to use the delimiter before and after the function (if you simply copy & paste my example, you should be fine).

How to use it?

  • First, you need to execute the function provided as a query (in PhpMyAdmin, Sequel Pro, whatever you prefer). This creates the function called 'wordcount';
  • then, use the function as a wrapper around a string. The returning result is the amount of words in that string. For example if you have a table called 'keywords' and within a field called 'Query':
UPDATE `keywords` SET `num_words` = wordcount(`Query`);

That's it.

I stole this beautiful snippet from stackoverflow.

And in PHP?

That's even easier, because PHP already has a function for this.

Need help?

If you need more help, take a look at the related service(s): Search analytics, Technical SEO.