Re: Suppress decimal point like digits in to_char?

On Mon, Mar 14, 2016 at 8:22 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Mar 14, 2016 at 3:31 AM, Francisco Olarte <folarte(at)peoplecall(dot)com>
> wrote:
>
>> Hi;
>>
>> On Mon, Mar 14, 2016 at 2:53 AM, David G. Johnston
>> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>> > On Sunday, March 13, 2016, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)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.
>>
>> 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.
>>
>>
> ​See also:
>
> http://www.postgresql.org/docs/9.5/interactive/functions-string.html
>
> ​
> format(formatstr text [, formatarg "any" [, ...] ])
>
> ​David J.​
>
>
>
Thanks for all the info and suggestions. I'll just observe that sure, you
can do it with a regex, but I'm still surprised that this can't be done
with to_char.

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 seems to me if the trailing 0s are
suppressed, the decimal should follow suit for whole numbers.

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.