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
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
\t, and return carriage
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.