(Table of contents & other stuff:
)
)
1 min.
MySQL word count
A neat UDF for counting words in a smart way, in MySQL.
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 $$Usage #
-
-
Execute the SQL above as a query
-
-
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: faster, but less smart #
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!