Get count of zeros in an integer using MySQL

Question

Let's say I have an integer value in MySQL (10090). I need to count all occurrences of the zero digit in that number. So for the previous case it would return 3:

select count_zeros(number) from dual;
-- when number = 10090, it return 3
-- when number = 10000, it return 4

How can I do that the fastest way using a MySQL query?

Answer 1

You can compare the string length with and without the character you want to count.

Solution using LENGTH

-- 0 in 10090: 3
-- 0 in 10000: 4
SELECT 
   (LENGTH(number) - LENGTH(REPLACE(number, '0', ''))) AS char_count
FROM dual;

A better and safer solution is to use the CHAR_LENGTH function instead of the LENGTH function. With CHAR_LENGTH function you can also count multi-byte characters (like §).

Solution using CHAR_LENGTH

-- § in 100§0: 1
SELECT 
    (CHAR_LENGTH(number) - CHAR_LENGTH(REPLACE(number, '§', ''))) AS char_count
FROM dual;

You can also extend the above solution to count for a string value using multiple characters.

-- 12 in 10120012: 2
SELECT number,
  FLOOR((CHAR_LENGTH(number) - CHAR_LENGTH(REPLACE(number, '12', ''))) / CHAR_LENGTH('12')) AS str_count
FROM dual;

demo on dbfiddle.uk

---

On MySQL you can create a function to use the above logic on a simpler way:

CREATE FUNCTION GetStringCount(strValue VARCHAR(255), strSearchValue VARCHAR(255))
RETURNS INT DETERMINISTIC NO SQL
RETURN FLOOR((CHAR_LENGTH(strValue) - CHAR_LENGTH(REPLACE(strValue, strSearchValue, ''))) / CHAR_LENGTH(strSearchValue));

You can use this new function GetStringCount like this:

-- example to count non-multi-byte character (here 0).
-- 0 in 10090: 3
-- 0 in 10000: 4
SELECT number, GetStringCount(number, '0') AS strCount
FROM dual;

-- example to count multi-byte character (here §).
-- § in 100§0: 1
SELECT number, GetStringCount(number, '§') AS strCount
FROM dual;

-- example to count a string with multiple characters.
-- 12 in 10120012: 2
SELECT number, GetStringCount(number, '12') AS strCount
FROM dual;