Using SQL to determine word count stats of a text field

by

The text handling capabilities of MySQL aren’t good enough for what you want. A stored function is an option, but will probably be slow. Your best bet to process the data within MySQL is to add a user defined function. If you’re going to build a newer version of MySQL anyway, you could also add a native function.

The “correct” way is to process the data outside the DB since DBs are for storage, not processing, and any heavy processing might put too much of a load on the DBMS. Additionally, calculating the word count outside of MySQL makes it easier to change the definition of what counts as a word. How about storing the word count in the DB and updating it when a document is changed?

Example stored function:

DELIMITER $$
CREATE FUNCTION wordcount(str LONGTEXT)
       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);
    SET idx = 1;
    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 ;

More Related Contents:

  1. get first occurrence of last changed value of a column [closed]
  2. MySQL – Rows to Columns
  3. ROW_NUMBER() in MySQL
  4. multiple query same table but in different columns mysql
  5. How can I make SQL case sensitive string comparison on MySQL?
  6. 1052: Column ‘id’ in field list is ambiguous
  7. How can I temporarily disable a foreign key constraint in MySQL?
  8. How to do a batch insert in MySQL
  9. Count the occurrences of DISTINCT values
  10. Creating a “Numbers Table” in MySQL
  11. SQL: selecting rows where column value changed from previous row
  12. Why do results from a SQL query not come back in the order I expect?
  13. How to generate data in MySQL?
  14. MySQL combine two columns and add into a new column
  15. SQL query of multiple values in one cell
  16. How to skip certain database tables with mysqldump?
  17. MySQL error 1241: Operand should contain 1 column(s)
  18. How to set a MySQL row to READ-ONLY?
  19. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  20. Mysql: Order by like?
  21. Getting first day of the week in MySql using Week No
  22. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  23. How to get a list of months between two dates in mysql
  24. How to format number with “.” as thousands separator and “,” as decimal separator?
  25. Using DISTINCT and COUNT together in a MySQL Query
  26. How to check if field is null or empty in MySQL?
  27. MySQL OPTIMIZE all tables?
  28. Strip last two characters of a column in MySQL
  29. Deleting a row based on the max value
  30. MySQL Multiple Joins in one query?

Leave a Comment