2

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Michael Owen
  • 365
  • 3
  • 20
  • Remember that an RDBMS is for the storage and retrieval of data, and not much else besides, so while you can do something like this in an RDBMS, it might not be your best option – Strawberry Jan 24 '20 at 22:25

2 Answers2

2

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
GMB
  • 216,147
  • 25
  • 84
  • 135
1

You can do it with reverse() like this:

select col, 
  length(col) - length(reverse(col) + 0) trailing_zeros
from tablename

replace col with the actual name of the column.
If there is a case of the column to contain only zeros then use this:

length(col) - length(reverse(col) + 0)  + (col = 0)

See the demo.
Results:

| col           | trailing_zeros |
| ------------- | -------------- |
| 2200040000000 | 7              |
| 1411258181000 | 3              |
forpas
  • 160,666
  • 10
  • 38
  • 76