Ask us if you have any question about PHP, MySQL, Apache,
Linux or optimizing your website for quick answers!!!

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 don't need to worry about it. It can be achieved 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

  1. SELECT SUM( LENGTH(name) - LENGTH(REPLACE(name, ' ', ''))+1)
  2. 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.

Still need help? Ask us

14 Comments »

  1. Patrick said,

    September 16, 2008 @ 3:22 am

    Great idea, very sneaky, and just what I needed. Thanks!

  2. jim cicon said,

    October 25, 2008 @ 7:09 pm

    Really slick, thanks for the tip

  3. 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 ?

  4. 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)

  5. Knut Urdalen said,

    April 24, 2009 @ 1:03 pm

    Very nice Wasif :) It saved my day! Thanks!

  6. 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.

  7. Ajay Arjunan said,

    May 12, 2009 @ 5:44 pm

    Great !!! Thanks for this query.

  8. 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!

  9. 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?

  10. 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

  11. F Laszlo said,

    June 8, 2011 @ 7:38 pm

    Well done!
    I’ve got inspiration and implemented this:

    select round((LENGTH(name) – LENGTH(REPLACE(name, ‘word’, ”))) /length(‘word’))
    from table;

    This count only words ‘word’.

  12. Mihai Vinatoru said,

    November 1, 2011 @ 5:14 pm

    Great. Thank you for this code snippet. You saved my day ;)

  13. mhehm said,

    November 12, 2011 @ 12:22 pm

    if column be empty, it show 1 and it is not correct

  14. F Laszlo said,

    November 19, 2011 @ 12:18 am

    Well, even if “column be empty” it works fine for me. Shows “0″.

RSS feed for comments on this post

Leave a Comment