Count substrings

from the Artful Common Queries page


To count instances of a search string in a target string ...
  • in the target string, replace the search string with a single character,
  • subtract the length of the modified target string from the length of the original target string,
  • divide that by the length of the search string:


SET @str  = "The quick brown fox jumped over the lazy dog"; 

SET @find = "the";

SELECT ROUND(((LENGTH(@str) - LENGTH(REPLACE(LCASE(@str), @find, '')))/LENGTH(@find)),0) 

AS COUNT; 

+-------+

| COUNT |

+-------+

|     2 |

+-------+



Note that REPLACE() does a case-sensitive search; to get a case-insensitive result you must coerce target and search strings to one case.

To remove decimals from the result:


SELECT CAST((LENGTH(@str) - LENGTH(REPLACE(LCASE(@str)), @find, '')))/LENGTH(@find) AS SIGNED) AS COUNT;




Return to the Artful Common Queries page