mysql count word in sql syntax

Question

Lets have a simple query:

SELECT myfield
FROM   mytable
WHERE  criteria

The above query gives us 10 rows of results for example. The field myField is a text field.

My question is this: is it possible to alter the above query and get the total word count of the myField field?

Edited: By total word count I mean the total word count of all selected fields in query, in all rows

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 |
+------------------------------+-------+

The question is very confusing. Please provide us with a small data example and the expected result. — Andrius Naruševičius, Aug 28 '12 at 10:08
@Andrius Naruševičius why is it confusing? I ask for a way to get total word count of a column. Where is the confusion? — andrew, Aug 28 '12 at 10:16
@andrew Could you please elaborate your question to remove the confusion..? What actually you want the total no of words in each cell of myfield or the total no of cells under myfeild fulfilling the criteria.? — heretolearn, Aug 28 '12 at 10:25
@andrew Please take a look at the accepted answer. Then compare the visual example, which is really easily understandable to the poorly worded sentence in the question (no offense). Also, due to the fact my comment got thumbed up several times, I would say it wasn't me who thought this way. Always try to add an example when possible to get rid of any potential misinterpretations. :) — Andrius Naruševičius, Aug 28 '12 at 11:20

score 25 · Accepted Answer · edited May 23 '17 at 12:17

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

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 the function work, you need to execute the declaration of the function in MySQL. It is just like executing any other query:

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 ;

Then it's a duplicate of http://stackoverflow.com/questions/748276/using-sql-to-determine-word-count-stats-of-a-text-field — Adi, Aug 28 '12 at 10:16
Yes, but perhaps OP doesn't know how to apply the function to their query. — Burhan Khalid, Aug 28 '12 at 10:19
Given that you have edit privileges, maybe you could've edited that answer to provide a usage example then linked to it in a comment. Maybe. — Adi, Aug 28 '12 at 10:21
@BurhanKhalid There was no example in the question itself. There was a query which did not help at all, because this could have been interpreted in at least 2 ways. Since you can edit everything what is valuable in the question, I suggest you moving your own example to the question. Otherwise, your answer could be a good wiki for all of us and we appreciate that, but it does not answer the question, because the question is poorly worded. — Andrius Naruševičius, Aug 28 '12 at 11:13

mysql count word in sql syntax

1 Answers1

Linked