# How to count decimals without zeros

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
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
``````
• @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:

``````SELECT LENGTH(TRIM(TRAILING '0' FROM SPLIT_PART(val::text, '.', 2)))
FROM t1;
``````

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