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)

  • it is numeric data type Aug 12 '19 at 8:05

PostgreSQL 13+

Starting on PostgreSQL 13 you can simply use trim_scale(numeric) function. This will remove the trailing 0s from a numeric column,

SELECT col, trim_scale(col) AS trim_scale
FROM (VALUES ('42.7'::numeric(10,5))) AS t(col);
   col    | trim_scale 
 42.70000 |       42.7

PostgreSQL 12 and lower.

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 

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


val         | scale
1.955000000 |     3
       1.10 |     1
     1.1010 |     3
   1.102030 |     5
  • @ypercubeᵀᴹ: to count the length you also need to remove all digits left of the decimal point which is a bit more complicated than to just remove trailing zeros Aug 12 '19 at 8:46

It's also possible to cast these values to text and use the SPLIT_PART function. (https://www.postgresql.org/docs/current/functions-string.html)

While still using TRIM and removing the trailing '0' characters, this would allow use of the LENGTH function to count the characters.

For example, this would return the scale:

FROM t1;

This will provide the same results as the previous answer provided by a_horse_with_no_name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.