Search code examples
mysqlsqlexcelcounttrailing

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.

Solution

  • 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