Mysql count word in sql syntax

Question

Allows you to specify a simple query:

SELECT myfield FROM mytable WHERE criteria 

The above query yields, for example, 10 rows of results. The myField field is a text field.

My question is this: is it possible to modify the above query and get the total number of words in the myField field?

Edited: The total number of words means the total number of words of all selected fields in the query, in all lines

Example

 +------------------------------+-------+ | sentence | Words | +------------------------------+-------+ | Hello World | 2 | | Hello World | 2 | | Mary had a little lamb | 5 | | Her fleece was white as snow | 6 | | Everywhere that mary went | 4 | | Umm, sheep followed her | 4 | +------------------------------+-------+ 

Answer 1

Use the excellent function from this question from @otis in your request:

 mysql> select * from test; +----+------------------------------+ | id | sentence | +----+------------------------------+ | 0 | Hello World | | 1 | Hello World | | 2 | Mary had a little lamb | | 3 | Her fleece was white as snow | | 4 | Everywhere that mary went | | 5 | Umm, sheep followed her | +----+------------------------------+ 6 rows in set (0.00 sec) mysql> SELECT sentence, wordcount(sentence) as "Words" from test; +------------------------------+-------+ | sentence | Words | +------------------------------+-------+ | Hello World | 2 | | Hello World | 2 | | Mary had a little lamb | 5 | | Her fleece was white as snow | 6 | | Everywhere that mary went | 4 | | Umm, sheep followed her | 4 | +------------------------------+-------+ 6 rows in set (0.02 sec) 

To make a function work, you need to execute the function declaration in MySQL. This is similar to executing any other request:

 mysql> DELIMITER $$ mysql> 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 $$ Query OK, 0 rows affected (0.10 sec) mysql> DELIMITER ;