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) 





|     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:


Return to the Artful Common Queries page