2

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)

DataLordDev
  • 63
  • 1
  • 7

2 Answers2

6

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
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
2

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.

Tyler
  • 142
  • 4