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
- How to make pagetext returns the concatenation of the appropriate myID rows (I'm looking to limit the concatenation to 25 rows only).
- How to count the number of words in the returned value.
Any ideas how to start?
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/
精彩评论