How to Count Occurrences of a String in MySQL

  • by
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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *