set global log_bin_trust_function_creators=1;
drop function if exists wordcount;
DELIMITER go
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
go
DELIMITER ;
For large text values, a word count function from the application language you are using (eg str_word_count() in PHP) is likely to deliver much better performance.Last updated 30 May 2024
