MySQL: Count Words in Column

April 13, 2010

I have been working on a rather large database that contains mostly textual page content. Out of curiosity I decided to see how many words are contained in the column with my content. I did not find a simple MySQL solution to finding the word count, but here is what I did come up with.

SELECT SUM( LENGTH( bodytext ) -  LENGTH( REPLACE( bodytext, ' ', '' ) ) +1 )
FROM tt_content

It worked great on the one page I tried it on. There were 5864 words. It has been running for about 5 minutes on the entire set now. Not the most efficient method out there I am sure, but I found it quick and it works.

To use it replace ‘bodytext’ with the column you are looking in, and ‘tt_content’ with the table the column is in.

Update

The query ran for 40 minutes before I terminated it. If you are using a large dataset I recommend looking at an alternative method, such as using PHP to count words in a column. Use that link to see the method I implemented