How to count the occurrence of a specific word in a Mysql db row?

Question

I am a newbie to PHP. I want to count the occurrence of a specific word in a MySQL db row. For example: There are two columns name and description.

---------------------------------------------------------------
| name | description                                          |
---------------------------------------------------------------
| abcd | My name ***is*** abcd. My father's name ***is*** xyz |
---------------------------------------------------------------
| xyzt | My name ***is*** xyz. I am a good girl.              |
---------------------------------------------------------------

I want to count the occurrence of the word 'is' from the 1st row description column. In the above example, 'is' occurs twice in the 1st row and once in the second row. Please guide me for the same.

For a SQL only answer, please see http://stackoverflow.com/questions/12344795/count-the-number-of-occurences-of-a-string-in-a-varchar-field — Fluffeh, Jun 12 '14 at 12:13
NOT voting to close this as a duplicate of the question linked above, since that question asks about strings while this question explicitly asks about *words* — symcbean, Jun 12 '14 at 12:21
Try `select count(*) from yourtable where match(description) against ('is');` borrowed from http://stackoverflow.com/a/396266/ or `select count(*) from table where fieldname REGEXP '[[:<:]]word[[:>:]]';` from http://stackoverflow.com/a/395859/ — Funk Forty Niner, Jun 12 '14 at 12:39

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

There are plenty of pure-SQL attempted solutions here on StackOverflow, but most of them count strings, not words. So let's reiterate:

SQL in general, and MySQL in particular, is not the best fit for the job. You'd better do it with PHP (if the result set is "small enough"):

$count = preg_match_all("/\\b$word\\b/", $input, &$matches);

Alternative, richer solutions exist based on str_word_count().

Note that it can be achieved in pure SQL:

with a stored procedure like this one.

In this example, replace the regex with [[:<:]]your_word[[:>:]] to match a specific word. Then just SELECT wordcount(your_column) FROM your_table. Improve the procedure by adding an input paramter (the word to count occurrences of). Then* RLIKE CONCAT('[[:<:]]', input_parameter, '[[:>:]]').
even without a stored procedure (but with the help of an auxiliary table).

In this example, replace HAVING word <> ' ' with HAVING word <> 'your_word' to count a specific word. GROUP BY word, maintable.id (instead of just word) to get a detailed cound by row. Add WHERE clauses to filter out the rows. Drawback: only a spaces are recognised as word delimiters.

I suppose the benefit of going for a PHP-based solution is obvious.

Raging Bull · Answer 2 · 2014-06-12T12:21:21.277

1

Query for this:

SELECT name,(CHAR_LENGTH(description)-
        CHAR_LENGTH(REPLACE(description,'is','')))/CHAR_LENGTH('is') AS TotalCount
FROM TableName
GROUP BY name

Result:

NAME    TOTALCOUNT
abcd    2
xyzt    1

Fiddle Demo

EDIT:

Since this query also counts any words which contains is (like 'this'), we can query for ' is ' (with one space before and one after).

SELECT name,(CHAR_LENGTH(description)-
        CHAR_LENGTH(REPLACE(description,' is ','')))/CHAR_LENGTH(' is ') AS TotalCount
FROM TableName
GROUP BY name

edited Jun 12 '14 at 12:21

answered Jun 12 '14 at 12:13

Raging Bull

18,593
13
50
55

1

Neat, unfortunately this will also count 'this', 'isoceles' and 'prism'. And mysql does not (AFAIK) have a regex replacement function (regexes can describe word boundaries) – symcbean Jun 12 '14 at 12:18
Thank you so much for the prompt reply. Will try it out soon :) – user3732838 Jun 12 '14 at 12:25
1

This will miss "Do you know what my name is?" and "Is it Bob?" – Strawberry Jun 12 '14 at 12:25

robinp7720 · Answer 3 · 2014-06-12T23:06:01.097

0

Try using substr_count:

substr_count ( string $haystack , string $needle)

It counts the amount of occurrences of a word in a string.

edited Jun 12 '14 at 23:06

answered Jun 12 '14 at 12:18

robinp7720

443
4
12

I got it directly form here: http://www.php.net//manual/en/function.substr-count.php – robinp7720 Jun 12 '14 at 23:06
This counts *string* (not *word*) occurrences.`substr_count ('foobar', 'foo')` returns `1`, but `0` is expected. – RandomSeed Jun 13 '14 at 07:18

score 0 · Answer 4 · answered Jun 12 '14 at 19:46

Pass all rows in array :

$query= "SELECT * from  yourtable "; 
$result= $mysqli->query($query);
while ($row = mysqli_fetch_assoc($result)) {
$rows[]=$row;
}

And transform array in array with count :

foreach ($rows as $row){
$name=$row['name'];
$description=$row['description'];
$myarr[$name]=substr_count($description, 'is');
}

You obtain desired array :

abcd => 2
xyzt => 1
...

How to count the occurrence of a specific word in a Mysql db row?

4 Answers4