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;

Last updated 17 Aug 2024