Count number of words in a MySQL column
It is a common requirement to count number of words in a column of a MySQL database table. But MySQL does not have any built in function to count number of words as it usually has for number of purposes.
But you can do it without it with a simple trick e.g. you have a column name and want to count number of words in it. This query will return number of words separated with space in a column
SELECT SUM( LENGTH(name) - LENGTH(REPLACE(name, ' ', ''))+1) FROM table
If you want to count number of words separated by some other string for example comma (,) or semicolon (;), simply replace space with your required character.
Patrick said,
September 16, 2008 @ 3:22 am
Great idea, very sneaky, and just what I needed. Thanks!
jim cicon said,
October 25, 2008 @ 7:09 pm
Really slick, thanks for the tip
Hussain said,
December 17, 2008 @ 2:13 pm
Thanks alot..
but if you have two spaces thogether .. it will be counted as 2 words
any one have solution ?
Wasif said,
December 17, 2008 @ 11:20 pm
To avoid such problem, first replace double spaces with a single space and then use this a solution may be (not tested)
LENGTH(REPLACE(REPLACE(name, ‘ ‘, ‘ ‘), ‘ ‘, ”))+1)
Knut Urdalen said,
April 24, 2009 @ 1:03 pm
Very nice Wasif
It saved my day! Thanks!
BL said,
May 4, 2009 @ 5:31 pm
The real solution is to replace double or more spaces in the first LENGTH():
SELECT SUM( LENGTH(REPLACE(REPLACE(name,’ ‘,’ ‘),’ ‘,’ ‘)) – LENGTH(REPLACE(name, ‘ ‘, ”))+1)
FROM table;
Replacing double or more spaces in the second LENGTH() won’t modify the result, because it has the same effect as the first solution has: all spaces are removed from text.
Ajay Arjunan said,
May 12, 2009 @ 5:44 pm
Great !!! Thanks for this query.
Al said,
May 22, 2009 @ 11:15 am
That’s bloody awesome – very nice thinking outside of the box. Works a treat! Many thanks saves me bring everything in and doing it with php!
cormac said,
August 21, 2009 @ 2:53 am
Has anyony managed an SQL query that will county the freuquency of words in column of a table?
Krisna said,
January 11, 2010 @ 7:09 pm
@cormac : did you mean counting a certain word each row? if that’s your mean try to read this
Imane said,
January 23, 2010 @ 12:39 am
GREAT tip, i am trying to use this select statement to show all the words and sort them by number of occurence (how many times that occur). Any help on this would be GREATLY appreciated.
Thanks again