Count occurrences of a word in string

Counting number of occurrences (or frequency) of a word in string

Excel

1. Using the function SUBSTITUTE & LEN functions. demo. Or
2. Using the function COUNTIF

MySQL way

SET @paragraph := 'an apple a day keeps the doctor away';
SET @term := 'apple';

SELECT FLOOR((LENGTH(@paragraph) - LENGTH(REPLACE(@paragraph, @term, ''))) / LENGTH(@term)) AS occurrences;

/* same with the following query */
SELECT FLOOR((CHAR_LENGTH(@paragraph) - CHAR_LENGTH(REPLACE(@paragraph, @term, ''))) / CHAR_LENGTH(@term)) AS occurrences;

online example

-- Count occurrences of a string: .
SET @input = "www.google.com";
SET @separator = ".";
SELECT (LENGTH(@input ) - LENGTH(REPLACE(@input , @separator, ""))) / LENGTH(@separator) AS count_of_separator;
-- expected result: 2

-- Count occurrences of a string: og
SET @input = "www.google.com";
SET @separator = "og";
SELECT (LENGTH(@input ) - LENGTH(REPLACE(@input , @separator, ""))) / LENGTH(@separator) AS count_of_separator;
-- expected result: 1

PHP

Using the mb_substr_count (binary safe) or substr_count functions. See details on demo.

$input = 'an apple a day keeps the doctor away';
$term = 'apple';

echo substr_count($input, $term);

BASH

Cygwin

• (1) separate each string by return_symbol (2) uniq command on Cygwin of Win or Linux : sort <file.txt> | uniq -c

file: test.txt

#apple
#追劇
#電影
#綜藝
#Apple
#藍芽

Result of the execution of command: sort test.txt | uniq -ic | sort -nr

 2 #Apple
   1 #電影
   1 #追劇
   1 #藍芽
   1 #綜藝

References

• mysql - Count the number of occurences of a string in a VARCHAR field? - Stack Overflow [Last visited: 2016-09-21]