Word count

from the Artful MySQL Tips List



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.

Return to the Artful MySQL Tips page