How to know how many words a paragraph contains in MySQL?

Question

What MySQL function can I use?

for example,

What Mysql function can I use?

contains 6 words.

Answer 1

There isn't a built in a function that I know if, but I found this in the comments of MySQL's String Functions documentation:

---

I was looking for word_count("string") in mysql, finally came up with an user defined function which is very usefull for me, note: I used for actual space.

DROP FUNCTION IF EXISTS word_count;
CREATE FUNCTION word_count (f_string text(5000)) RETURNS smallint(10)
BEGIN
DECLARE new_string text(5000);
WHILE INSTR(f_string,'<space><space>')>0
DO
SET new_string=(select REPLACE(f_string,'<space><space>','<space>'));
SET f_string=new_string;
END WHILE;

RETURN (select LENGTH(TRIM(f_string))-LENGTH(REPLACE(TRIM(f_string),'<space>',''))+1);
END
//

Here is the result

mysql> select word_count("Balaji Devarajan") WORD_COUNT;

+------------+
| WORD_COUNT |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

mysql> select word_count(" Balaji Devarajan ") WORD_COUNT;

+------------+
| WORD_COUNT |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

mysql> select word_count("Balaji Devarajan") WORD_COUNT;

+------------+
| WORD_COUNT |
+------------+
|          2 |
+------------+
1 row in set (0.01 sec)

Answer 2

There is no function to count words in MySQL (or ANSI SQL, or any other DBMS I'm familiar with).

You could maybe fake it by counting the number of spaces in the text using a string replace:

SELECT LENGTH(colname)-LENGTH(REPLACE(colname, ' ', ''))+1 AS wordcount FROM tablename;

This isn't really a word count but would work as long as every word is separated by exactly one space.

To get better word matching you would need a regex, but there is no regex replace in MySQL so you can't use the replace trick. You could select specifically 6-word long values using a REGEXP/RLIKE match though:

SELECT * FROM tablename WHERE colname RLIKE '^[^[:alnum:]]*[[:alnum:]]+([^[:alnum:]]+[[:alnum:]]+){5}[^[:alnum:]]*$';

Either way, this is slow. It will have to do a string replace or regex match on every row of the table every time you do the query. If number-of-words is a query you are doing often you will want to optimise (denormalise) the table by adding a (possibly indexed) column to store the number of words.