Geek asks and answers

Mysql query to count spaces or words from a whole line

in my database there is one table named name as tableA. The table structure is given here.

id      from     message
1       23       hi how are you
2       65       hey whats going on
3       74       enjoying the vacation with family
4       23       here in Australia its chilly season
5       74       hey sam whats the plan for tomorrow

see identifier - auto-increment.from column says which user sends the message, and the message column consists of messages. I want to know which users sent how many words. So I must first count the words from the row, then I have to search the entire column and find where there is repetition, and add the words for this particular user. In my example, you can see that two places come from 23 to 74. I want my result to be

    from       nos
    23         10
    65         4 
    74         12

here you can see that 23 goes in two lines, so I added word words from two lines, such as 74, also goes in two lines. User guide

+3
3

-

Select length('string')-length(replace('string',' ',''))+1;


Select *,(length(message)-length(replace(message,' ',''))+1) from table_name

.

, -

Select `from`, sum((length(message)-length(replace(message,' ',''))+1)) as nos from table_name group by `from`
+7

, , :

SUM( LENGTH(message) - LENGTH(REPLACE(message, ' ', ''))+1)

. , ( ). - :

select `from`, SUM( LENGTH(message) - LENGTH(REPLACE(message, ' ', ''))+1) as nos
from tableA
group by `from`
+1
SELECT from, SUM(LENGTH(message) - LENGTH(REPLACE(TRIM(message), ' ', ''))+1) FROM my_table GROUP BY from;
0
source

More articles:


All Articles