(Update )

MySQL word count function

A MySQL function that you can use to count the number of words in a text string. 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', use: 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.

What's next?

Not much really. But if you think this was useful to you, it might be for others also: