Omar Abid Omar Abid - 1 month ago 10
MySQL Question

Calculate the words number in a view in mysql

I have the following table structure

+-----------------+-------------+
| myID | text |
+-----------------+-------------+
| 3 | some text |
| 3 | other text |
| 5 | text |
+-----------------+-------------+


myID is not a unique ID. It can be repeated. Text is arbitrary strings.
I'm looking to create a view that returns the count of words in the text of the myID rows.

+-----------------+-------------+
| myID | count |
+-----------------+-------------+
| 3 | 4 |
| 5 | 1 |
+-----------------+-------------+


Well, I started out (I'm a newbie is mySQL) and did the following

SELECT
myid,
pagetext
FROM table
GROUP by myid


This does just the grouping. I think the next steps are


  1. How to make pagetext returns the concatenation of the appropriate myID rows (I'm looking to limit the concatenation to 25 rows only).

  2. How to count the number of words in the returned value.



Any ideas how to start?

Answer

You can find the number of words in a column (if you can assume there is 1 and exactly 1 space between words) with the following query:

SELECT
  myId,
  SUM(LENGTH(text)-LENGTH(REPLACE(text, ' ', ''))+1)
FROM table
GROUP BY myId

Source: http://www.mwasif.com/2008/12/count-number-of-words-in-a-mysql-column/

Comments