MySQL. Counting Occurrences of a Substring in a String

A friend of mine needed to count the occurrences of substrings in a string using MySQL. Since MySQL has no such function, I put together my own query.

In terms of functionality, it is equivalent to the PHP function substr_count.

So, let’s look at the implementation using my friend’s source data as an example:

• “(1)(29)(330)” — let’s say this is a string in the result set;
• “)” — the delimiter we are using.

select
  (char_length('(1)(29)(330)') - char_length(replace('(1)(29)(330)',')','')))
div
  char_length(')');

1. Everything here is straightforward: we calculate the difference between the number of characters in the complete string

   char_length('(1)(29)(330)')
   

   and the number of characters in the string with our delimiter replaced by an empty string

   replace('(1)(29)(330)',')','')
   

2. We count the number of characters in our delimiter

   char_length(')')
   

Then we calculate the integer remainder from dividing (1) by (2).

As a result, we get the number “3”.