Count the number of words with MySQL

I was looking for a way to count the number of words in a blog post using MySQL. Most solutions I found online required I create a function. I wanted something simpler instead, something as close to a native function as possible. And the answer that occurred to me was to use the Length() and Replace() function with a catch.

In a string, words are separated by a space or new lines. To identify the number of words, we need to count the number of characters in a string and count the characters without the spaces and new lines. When we subtract the two, we get the word count. Let's look at an example.

var text = "sphinx of black quartz,\njudge my vow";

// total characters
var characterCount = text.length;

var nonspaceCharacterCount = text.replace(/\s/g, "").length;

var wordcount = characterCount - nonspaceCharacterCount + 1;
// wordcount = 7 words

Converting this to MySQL is trivial. We just have to pay attention to all space characters like the new line \n, tab \t, and return carriage \r:

SELECT 
    id,
    LENGTH(content) - 
    LENGTH(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        content,
                        "\r", ""
                    ),
                    "\n", ""
                ),
                "\t", ""
            ),
            " ", ""
        )
    ) + 1 AS wordcount
FROM blog_post

The query will return the number of words in each content row as wordcount. Now of course, you can wrap this in a function called wordcount() and make your life easier.