Thread: Suppress decimal point like digits in to_char?

Hi.  Is there a way with to_char to suppress a decimal point, like a leading or trailing 0, so that integers will not have them, but non-ints will?  I'm hoping I'm missing something easy.  Thanks.

Ken

SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM

( SELECT 1 AS val UNION SELECT 1.05 AS val) foo;

 val  | to_char 

------+---------

    1 | 1.

 1.05 | 1.05

On Mar 13, 2016 6:29 PM, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
>
> On Sunday, March 13, 2016, Ken Tanzer <ken.tanzer@gmail.com> wrote:
>>
>> Hi.  Is there a way with to_char to suppress a decimal point, like a leading or trailing 0, so that integers will not have them, but non-ints will?  I'm hoping I'm missing something easy.  Thanks.
>>
>> Ken
>>
>> SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM
>> ( SELECT 1 AS val UNION SELECT 1.05 AS val) foo;
>>
>>  val  | to_char 
>> ------+---------
>>     1 | 1.
>>  1.05 | 1.05
>>
>>
>
> Not seeing a native way to do so - and I'd question doing so as a general rule - though you know your domain.  If you must have this you will want to utilize regexp_replace to identify the situation and replace it.  A simple "\.$" check and a substring would work also.
>
> David J.

Thanks David. Just curious what part of this you would question.  The case for numbers, currency in particular, coming out with a decimal and pennies when present, and as whole dollars when not (and without a decimal place at the end) seems pretty common and clear cut.  What am I missing in your question?

Cheers,
Ken

On Mar 13, 2016 6:29 PM, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
>
> On Sunday, March 13, 2016, Ken Tanzer <ken.tanzer@gmail.com> wrote:
>>
>> Hi.  Is there a way with to_char to suppress a decimal point, like a leading or trailing 0, so that integers will not have them, but non-ints will?  I'm hoping I'm missing something easy.  Thanks.
>>
>> Ken
>>
>> SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM
>> ( SELECT 1 AS val UNION SELECT 1.05 AS val) foo;
>>
>>  val  | to_char 
>> ------+---------
>>     1 | 1.
>>  1.05 | 1.05
>>
>>
>
> Not seeing a native way to do so - and I'd question doing so as a general rule - though you know your domain.  If you must have this you will want to utilize regexp_replace to identify the situation and replace it.  A simple "\.$" check and a substring would work also.
>
> David J.

Thanks David. Just curious what part of this you would question.  The case for numbers, currency in particular, coming out with a decimal and pennies when present, and as whole dollars when not (and without a decimal place at the end) seems pretty common and clear cut.  What am I missing in your question?

Hi;

On Mon, Mar 14, 2016 at 2:53 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Sunday, March 13, 2016, Ken Tanzer <ken.tanzer@gmail.com> wrote:
....
> Typically if I'm going to format any currency amount with pennies I would
> format all values, even those with zero pennies, to the same precision.
> Typically when displaying such amounts I'd right-justify the values and thus
> cause the decimals to line up.

I do format with the .00 too, just wanted to point that the lining up
is easy if you just substitute '.00$' or '\.$' with the correct amount
of space, something like:

s=> select val, tc, '"'||tc||'"' as quoted,
regexp_replace(tc,'\.00$','   ') as replaced from (select val,
to_char(val::decimal(6,2),'999,999D99') as tc from (values
(1),(1.05),(0)) as v(val)) as w;
 val  |     tc      |    quoted     |  replaced
------+-------------+---------------+-------------
    1 |        1.00 | "       1.00" |        1
 1.05 |        1.05 | "       1.05" |        1.05
    0 |         .00 | "        .00" |
(3 rows)

Although I dislike 0 as space, so I normally use '99990':

s=> select val, tc, '"'||tc||'"' as quoted,
'"'||regexp_replace(tc,'\.00$','   ')||'"' as replaced from (select
val, to_char(val::decimal(6,2),'999,990D99') as tc from (values
(1),(1.05),(0)) as v(val)) as w;
 val  |     tc      |    quoted     |   replaced
------+-------------+---------------+---------------
    1 |        1.00 | "       1.00" | "       1   "
 1.05 |        1.05 | "       1.05" | "       1.05"
    0 |        0.00 | "       0.00" | "       0   "
(3 rows)

And, if you want to use FM but make them line up on the right is doable too:

s=> select val, tc, '"'||tc||'"' as quoted,
'"'||regexp_replace(tc,'\.$','   ')||'"' as replaced from (select val,
to_char(val::decimal(6,2),'FM999,990D99') as tc from (values
(1),(1.05),(0)) as v(val)) as w;
 val  |  tc  | quoted | replaced
------+------+--------+----------
    1 | 1.   | "1."   | "1   "
 1.05 | 1.05 | "1.05" | "1.05"
    0 | 0.   | "0."   | "0   "
(3 rows)

But a right-aligning string output routine needs to be used.


cdrs=> select val, tc, '"'||tc||'"' as quoted,
'"'||regexp_replace(tc,'\.$','   ')||'"' as replaced from (select val,
to_char(val::decimal(6,2),'FM999,990D99') as tc from (values
(1234),(1.05),(0)) as v(val)) as w;
 val  |   tc   |  quoted  |  replaced
------+--------+----------+------------
 1234 | 1,234. | "1,234." | "1,234   "
 1.05 | 1.05   | "1.05"   | "1.05"
    0 | 0.     | "0."     | "0   "
(3 rows)

Summarising, any combination can be easily done with a single round of replace.

Francisco Olarte.

Hi David:

On Mon, Mar 14, 2016 at 4:22 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

>> But a right-aligning string output routine needs to be used.
...

>> Summarising, any combination can be easily done with a single round of
>> replace.

> See also:
> http://www.postgresql.org/docs/9.5/interactive/functions-string.html
> format(formatstr text [, formatarg "any" [, ...] ])

You mean to use it to right align the replaced string ( i.e.
format('%12s',replace(...)) ) or is there a code I do not know off
which can be used to achieve the global result ( I use it but it seems
to be like a restricted sprintf which can not do the supress the zero
stuff ) ?

Francisco Olarte.

Hi Ken:

On Mon, Mar 14, 2016 at 7:33 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:

> Thanks for all the info and suggestions.  I'll just observe that sure, you can do it with a regex, but I'm still
surprisedthat this can't be done with to_char. 

Well, this may be a good enhancement request, add something like
d=decimal point, supressed if alone.

> In particular, one might reasonably choose a format string like 'FM999,999D99' and not realize it will fail on whole
numbers. Is there any particular reason the D is not suppressible in this case, either by default or as an option?  It
seemsto me if the trailing 0s are suppressed, the decimal should follow suit for whole numbers. 

It does not fail, it just works in a diffrent way of what you would
like. Regarding supression, IMO it's a bad thing, it can lead to
misleading results. Imagine it is, and you do a right aligned print (
usual for numbers ) of prices 5.45, 1.20, 99.00, 2.40, and you end up
with ( using x for align )
price:
------
xx5.45
xx1.20
xxxx99
xx2.40

It would be misleading, I prefer to have xxx99., ugly but clearer IMO
( of course one never supress decimals in prices, so I would use
990D00, but anyway ).

Francisco Olarte.

HI:

On Mon, Mar 14, 2016 at 8:02 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
>> price:
>> ------
>> xx5.45
>> xx1.20
>> xxxx99
>> xx2.40
...
> I appreciate the comment and explanation.  But your example shows numbers where the trailing 0s are not suppressed.

Yeah, my fault, but 5.45, 1.25, 99.00, 2.45 will create a that kind of
alignment which stronly suggest its .99. Of course it's not printed as
such, but visually it can trick you, that's why decimal points are
never supressed anc softwar has options to align coluns to the decimal
point.

> It seems to me that if you're requesting suppression of trailing 0s, then you're accepting that your numbers aren't
goingto align in the first place. And so it's hard for me to see how, for example "99." is ever going to be desirable
outputif suppression is what you're after. 

Never desirable for me, but I never use d9, I always do d0, but you are right.

> And just as context on my end, the times I use to_char are generally to merge numbers into a document or some
fragmentof English text. 

For the grouping ',' I see your point, I normally just use defaut
conversion for these as I dislike the grouping.

As I said, I could see a legitimitate case for Dd similar to the 09
stuff, but having so many replace options ( I think you can even do
<trim(trailing '.' from to_char(number, 'FM9999D99'))>, which is
easier on the eye but fails on locales, as the regexp does ( as a
note, in Spain they are inverted, dot for grouping comma for decimals
) ) I do not think it's a big deal, uglier things are coded by me
continuously nearly via muscle memory.

Francisco Olarte.