0

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 – DataLordDev Aug 12 at 8:05
4

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
  • or "convert it to a string, remove trailing zeros and count the length after the dot" – ypercubeᵀᴹ Aug 12 at 8:45
  • @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 – a_horse_with_no_name Aug 12 at 8:46
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.

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.