Mysql + count all words in a Column

Question

I have 2 columns in a table and I would like to roughly report on the total number of words. Is it possible to run a MySQL query and find out the total number of words down a column.

It would basically be any text separated by a space or multiple space. Doesn't need to be 100% accurate as its just a general guide.

Is this possible?

Answer 1

Try something like this:

SELECT COUNT(LENGTH(column) - LENGTH(REPLACE(column, ' ', '')) + 1)
FROM table

This will count the number of caracters in your column, and substracts the number of caracters in your column removing all the spaces. Hereby you know how many spaces you have in your row and hereby know how many words there are (roughly because you can also type in a double space, this wil count as two words but you say you want it roughly so this should suffice).

Answer 2

Count simply gives you the number of found rows. You need to use SUM instead.

SELECT SUM(LENGTH(column) - LENGTH(REPLACE(column, ' ', '')) + 1) FROM table

Answer 3

A less rough count:

SELECT LENGTH(column) - LENGTH(REPLACE(column, SPACE(1), '')) 
FROM
  ( SELECT CONCAT(TRIM(column), SPACE(1)) AS column
    FROM
      ( SELECT REPLACE(column, SPACE(2), SPACE(1)) AS column
        FROM 
          ( SELECT REPLACE(column, SPACE(3), SPACE(1)) AS column
            FROM 
              ( SELECT REPLACE(column, SPACE(5), SPACE(1)) AS column
                FROM 
                  ( SELECT REPLACE(column, SPACE(9), SPACE(1)) AS column
                    FROM 
                      ( SELECT REPLACE(column, SPACE(17), SPACE(1)) AS column
                        FROM 
                          ( SELECT REPLACE(column, SPACE(33), SPACE(1)) AS column
                            FROM tableX
                          ) AS x
                      ) AS x
                  ) AS x
              ) AS x
          ) AS x
      ) AS x
  ) AS x