Number of Word on each row MySQL Query

Hi, now i’m gonna share my problems and my solve for that problems. It’s welcome if you know about other solution of this problems and want to share it, just comment this post below.

The problem is how to count a word on each record. The example, there is an article table which i want to know how many word ‘MySQL’ on each row of that table. We cannot use COUNT() function because that function is counting the number of row which contain ‘MySQL’ if we use the query like :

SELECT COUNT(*) FROM article WHERE article_body LIKE '%MySQL%'

Okay, here is the article table :

article_id article_body
1 WordPress is the most popular Content Management System for blog engine. It just need webserver with PHP and MySQL and then just run it and the script for MySQL database will executed automatically
2 MySQL is one of database management system. MySQL community is free version of MySQL. This version of MySQL, already enough for developing application

We know that word ‘MySQL’ shown 2 times on article 1 and shown 4 times on article 2. If we use the COUNT() query above, we just get result : 2. So, how we can get that true result? On this solution, i use a simple mathematical formula to solve the problems.

WORD_COUNT = {LENGTH(WHOLE_ARTICLE) – LENGTH(WHOLE_ARTICLE WITHOUT WORD)} / LENGTH(WORD)

From the formula above, we can write it on SQL Query like :

SELECT
   article_body,
   @all := length(article_body) as whole_length,
   @word := length('MySQL') as word_length,
   @rep := length(replace(article_body,'MySQL','')) as wihout_word,
   @count := (@all - @rep)/@word as word_count
FROM article

Just see this type of query and feel confuse? I’ll try to explain about it.

  1. @all, @word, etc is a variable that can be assigned by := operator
  2. length() is a function that count the number of character || length(field or text)
  3. replace() is a function that return replaced text || replace(field or text, text to find, replacement text)

And here is the result :

article_body whole_length word_length wihout_word word_count
WordPress is the most popular Content Management S… 197 5 187 2
MySQL is one of database management system. MySQL … 150 5 130 4

One Thought on “Number of Word on each row MySQL Query

  1. great post! learn a lot but slight problems, you should mention that using replace(), it a bit case-sensitive.. i tried and after i make some changes, it works! just want to share, dunno either it just version problems or yours..fyi,my version is mySQL 5.5..that’s all,thanks~

Leave a Reply

Your email address will not be published. Required fields are marked *

Post Navigation