Count occurrences of a word in string: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
No edit summary
Line 2: Line 2:


== Excel ==
== Excel ==
Using the function [https://support.office.com/zh-tw/article/substitute-%E5%87%BD%E6%95%B8-6434944e-a904-4336-a9b0-1e58df3bc332 SUBSTITUTE] & [https://support.office.com/zh-hk/article/len%E3%80%81lenb-%E5%87%BD%E6%95%B8-29236f94-cedc-429d-affd-b5e33d2c67cb LEN] functions.
# Using the function [https://support.office.com/zh-tw/article/substitute-%E5%87%BD%E6%95%B8-6434944e-a904-4336-a9b0-1e58df3bc332 SUBSTITUTE] & [https://support.office.com/zh-hk/article/len%E3%80%81lenb-%E5%87%BD%E6%95%B8-29236f94-cedc-429d-affd-b5e33d2c67cb LEN] functions. [https://docs.google.com/spreadsheets/d/1eg5XuGs8X7bS_RrFECx8ktuPgTwpV12UPfkdlpbHlMU/edit?usp=sharing demo]. Or
[https://docs.google.com/spreadsheets/d/1eg5XuGs8X7bS_RrFECx8ktuPgTwpV12UPfkdlpbHlMU/edit?usp=sharing demo]
# Using the function [https://support.office.com/zh-hk/article/countif-%E5%87%BD%E6%95%B8-e0de10c6-f885-4e71-abb4-1f464816df34 COUNTIF]


== MySQL way ==
== MySQL way ==
Line 14: Line 14:
/* same with the following query */
/* same with the following query */
SELECT FLOOR((CHAR_LENGTH(@paragraph) - CHAR_LENGTH(REPLACE(@paragraph, @term, ''))) / CHAR_LENGTH(@term)) AS occurrences;
SELECT FLOOR((CHAR_LENGTH(@paragraph) - CHAR_LENGTH(REPLACE(@paragraph, @term, ''))) / CHAR_LENGTH(@term)) AS occurrences;
</pre>
[http://sqlfiddle.com/#!9/6d06f/480/2 online example]
<pre>
-- 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
</pre>
</pre>


[http://sqlfiddle.com/#!9/6d06f/480/2 online example]


== PHP ==
== PHP ==
Using the [https://www.php.net/manual/en/function.mb-substr-count.php mb_substr_count] ('''binary safe''') or [https://www.php.net/manual/en/function.substr-count.php substr_count] functions. See details on [http://sandbox.onlinephpfunctions.com/code/326ff9f24ebfea9f67ddf2f1475a6172cdcd2e66 demo].
Using the [https://www.php.net/manual/en/function.mb-substr-count.php mb_substr_count] ('''binary safe''') or [https://www.php.net/manual/en/function.substr-count.php substr_count] functions. See details on [http://sandbox.onlinephpfunctions.com/code/326ff9f24ebfea9f67ddf2f1475a6172cdcd2e66 demo].
<pre>
<pre>
$input = 'an apple a day keeps the doctor away';
$input = 'an apple a day keeps the doctor away';
Line 29: Line 41:
</pre>
</pre>


[[Category:Software]] [[Category:Programming]] [[Category:Data Science]] [[Category:Text file processing]]
 
== References ==
* [http://stackoverflow.com/questions/12344795/count-the-number-of-occurences-of-a-string-in-a-varchar-field mysql - Count the number of occurences of a string in a VARCHAR field? - Stack Overflow] {{access | date = 2016-09-21}}
 
[[Category:Software]] [[Category:Programming]] [[Category:Data Science]] [[Category:Text file processing]] [[Category:Data transformation]] [[Category:MySQL]]

Revision as of 10:16, 25 August 2019

Count occurrences 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);


References

Retrieved from "https://wiki.planetoid.info/index.php?title=Count_occurrences_of_a_word_in_string&oldid=20869"