Case-Sensitive Occurrence Function
Since there's no function to count occurrences in strings, so I create a customized function to meet the requirement as followings:
create function count_occurrences_case_sensitive(whole_string varchar(2048), match_string varchar(255)) returns int(11)
begin
return round((char_length(whole_string) - char_length(replace(whole_string, match_string, ''))) / char_length(match_string));
end;
Please note that, this function is multi-byte safe and case-sensitive because the function char_length() and replace() are multi-byte safe in nature, additionally, replace() operates strings case-sensitively.
Case-Insensitive Occurrence Function
If you want a case insensitive function, you need to rewrite the customized function as this:
create function count_occurrences_case_insensitive(whole_string varchar(2048), match_string varchar(255)) returns int(11)
begin
return round((char_length(whole_string) - char_length(replace(lower(whole_string), lower(match_string), ''))) / char_length(match_string));
end;
As you can see, we use lower() to make all strings lower-cased, then we count the occurrences.
Of course, we can also count the occurrences of strings in the middle tier like PHP engine instead of the database tier. But I prefer to get everything ready as much as possible in the database tier before responding the result to the middle tier in order to reduce the round trips between tiers.