PostgreSQL – How to Count Decimals Without Zeros

decimalpostgresqlquery

How can I count all decimal place up to the first zero

For example I have a column named x with an entry of numeric datatype value 1.955000000

I want to count the decimal places without the zeros. So here I want the query to make an output of 3 not 9 (with zeros)

Best Answer

The only thing I can think of, is to convert the value to a string, remove all trailing zeros, convert it back to a numeric and then use the scale() function:

scale(trim(trailing '0' from the_column::text)::numeric)

The following example:

create table t1 (val numeric);

insert into t1 
values 
  (1.955000000), 
  (1.10), 
  (1.1010), 
  (1.102030);

select val, scale(trim(trailing '0' from val::text)::numeric)
from t1;

returns:

val         | scale
------------+------
1.955000000 |     3
       1.10 |     1
     1.1010 |     3
   1.102030 |     5
Related Question