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).
UPDATE `keywords` SET `num_words` = wordcount(`Query`);
I stole this beautiful snippet from stackoverflow.
That's even easier, because PHP already has a function for this.
If you think this was useful to you, it might be for others also. Feel free to share: