Skip to content
Home » MySQL » MySQL String Occurrence Count

MySQL String Occurrence Count

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.

Leave a Reply

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