How to check whether all my characters are same in MYSQL-CodePudding

I have a set of numbers and some numbers are starts with 000 and some numbers are contains only zeros like below.

select number,count(*) as attempts from num  where number like '0000%' group by number;

 ------------------ ------- 
| number           | attempts |
 ------------------ ------- 
| 0000             |     3 |
| 000000           |    16 |
| 00005124865151   |     1 |
| 0000000          |     1 |
| 00008816081588   |     1 |
 ------------------ ------- 
5 rows in set (1.40 sec)

Are there any method to get all zero numbers and numbers starts more than 3 zeros separately? I need all zeros as 20 and more than 3 zero numbers as 2.

Here is my try.. But seems this is wrong..

select date(date),
count(case when number like '0000%' then 1 else null end) as case1,
count(case when number like '0000%0' then 1 else null end) as case2 
from num  group by  date(date);

CodePudding user response：

To test if it's only zeroes, use a regular expression

SUM(number REGEXP '^0 $') AS case2