1 min.
Code first; text later #
Don't forget to use the delimiter before and after the function (if you simply copy & paste my example, you should be fine).
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:]]' OR SUBSTRING(str, idx, 1) RLIKE "'";
IF NOT prevChar AND currChar THEN
SET wordCnt=wordCnt+1;
END IF;
SET prevChar=currChar;
SET idx=idx+1;
END WHILE;
RETURN wordCnt;
END
$$How to use it? The UDF #
-
1.
First, you need to execute the function provided as a query (in PhpMyAdmin, Sequel Pro, whatever you prefer). This creates the function called 'wordcount';
-
2.
Next, 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.
Alternative: make it faster #
A faster, but less accurate way is to count the number of spaces, and divide by that (this does not take abbreviations into account, for example):
SELECT
ROUND (
(
CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE (content, " ", ""))
)
/ CHAR_LENGTH(" ")
) AS count
FROM documentsUse the one that suits you best!