On Mon, Mar 14, 2016 at 8:22 AM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
Hi;
On Mon, Mar 14, 2016 at 2:53 AM, David G. Johnston
<david.g.johnston@xxxxxxxxx> wrote:
> On Sunday, March 13, 2016, Ken Tanzer <ken.tanzer@xxxxxxxxx> 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: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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
