Skip to content
Advertisement

Is there a way to count all the trailing zeros in a column in MySQL?

I am looking to find out if there is a way to count the number of trailing zeros in a column. The column is made up of a maximum of 13 numbers as shown below and I want to count all the zeros before an actual number but not sure how to go about doing this.

Example numbers
2200040000000 -- this would have the value 7
1411258181000 -- this would have the value 3

I have managed to do this in Excel but I am trying to do this directly in MySQL in a query rather than importing the data in Excel and then writing the query as theres more steps to go through.

The following is the formula:

=6+RIGHT(TEXT(B17,"0.##############E+00"),2)-LEN(TEXT(B17,"0.##############E+00"))

I would really appreciate it, if somebody could advise on how I could resolve this issue as would really help me to move forward and not go back and forth with Excel.

Advertisement

Answer

You could use string function TRIM(), which is available since the early days of MySQL:

char_length(num) - char_length(trim(trailing '0' from num))

trim(...) removes trailing 0s from the string; the difference of lengh between the original value and the trimed value gives you the number of trailing 0s in the string.

Demo on DB Fiddle:

create table t (num bigint);
insert into t values (2200040000000), (1411258181000);

select 
    num,
    char_length(num) - char_length(trim(trailing '0' from num)) cnt_trailing_0s
from t;

          num | cnt_trailing_0s
------------: | --------------:
2200040000000 |               7
1411258181000 |               3
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement