D
D
di2019-10-14 16:57:23
PostgreSQL
di, 2019-10-14 16:57:23

How to format the result so that it is without non-significant zeros?

There is a table newtable with one column c1 type numeric (20,6)
5da47bdae8da8412752843.jpeg
I need to return the result formatted as follows - instead of 0 there is a space, the rest of the numbers are rounded to hundredths, insignificant zeros are removed.
While there is such a request

select 
  case
    when c1 = 0 then ''
    else c1::numeric(20,2)::text
  end result
from newtable

And this query returns me everything in the correct form, except that it returns insignificant 0.
5da47e6ef3f44088040249.jpeg
I want to get 24, not 24.00.
Is this possible using postgres formatters, and if so, how?
UPD. There is such an option, but it just cuts off 2 zeros. So for 300 I will get a result of 3 instead of the expected 300
select 
  case
    when c1 = 0 then ''
    else trim(trailing '.00' from c1::numeric(16,2)::text)
  end result
from newtable

Answer the question

In order to leave comments, you need to log in

2 answer(s)
D
di, 2019-10-14
@Delgus

There is such an option. but it looks pretty creepy

select 
  case
    when c1 = 0 then ''
    else rtrim(rtrim(c1::numeric(16,2)::text,'0'),'.')
  end result
from newtable

S
Sergey c0re, 2019-10-15
@erge

You can also do this:

select 
  case
    when c1 = 0 then ''
    else rtrim(to_char (c1, 'FM9999999999999999D99'), '.')
  end result
from newtable

see Data Type Formatting Functions

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question