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;

Answer 2

I think the first thing to be done is, casting those integer values to string.

https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_cast

Then find occurences of a certain char

https://lists.mysql.com/mysql/215049

mysql> create table numbers(x int);
Query OK, 0 rows affected (0,38 sec)
mysql> select * from numbers;
+-----------+
| x         |
+-----------+
|    123000 |
|      1300 |
|    135600 |
| 135623400 |
|     13560 |
|    135160 |
|  13514560 |
|   1351120 |
|  13512310 |
+-----------+
9 rows in set (0,00 sec)

Find occurences of zero

mysql> select x, round((length(cast(x as char(11))) - length( replace( cast( x as char(11) ), "0", "" ) ))/length("0")) as str_x from numbers limit 5;
+-----------+-------+
| x         | str_x |
+-----------+-------+
|    123000 |     3 |
|      1300 |     2 |
|    135600 |     2 |
| 135623400 |     2 |
|     13560 |     1 |
+-----------+-------+
5 rows in set (0,00 sec)

Find thirteens

mysql> select x, round((length(cast(x as char(11))) - length( replace( cast( x as char(11) ), "13", "" ) ))/length("13")) as str_x from numbers;
+-----------+-------+
| x         | str_x |
+-----------+-------+
|    123000 |     0 |
|      1300 |     1 |
|    135600 |     1 |
| 135623400 |     1 |
|     13560 |     1 |
|    135160 |     1 |
|  13514560 |     1 |
|   1351120 |     1 |
|  13512310 |     1 |
| 132134534 |     2 |
+-----------+-------+
10 rows in set (0,00 sec)

mysql>