开发者

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?


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/

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜