Difference between revisions of "Count occurrences of a word in string"

From LemonWiki共筆
Jump to: navigation, search
m (MySQL way)
 
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]]

Latest revision as of 10:16, 25 August 2019

Count occurrences of a word in string

Excel[edit]

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

MySQL way[edit]

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[edit]

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[edit]