Re: to_char with locale decimal separator

On Mon, Jul 29, 2013 at 1:24 PM, Ingmar Brouns <swingi(at)gmail(dot)com> wrote:
> Hi,
>
> I need to convert some numerical values to text using the decimal
> separator that corresponds to the current locale. However, I do
> not want to lose information by padding with zero decimals or
> truncating zero decimals. So I basically want a text cast that
> also replaces the dot by a comma. I've looked at the to_char
> function and the formatting patterns, but when using those I
> either add or truncate zero decimals.
>
>
> # show lc_numeric;
> lc_numeric
> ------------
> nl_NL.utf8
> (1 row)
>
> # select 1.500::text;
> text
> -------
> 1.500
> (1 row)
>
> # select to_char(1.500, '999999D99999999');
> to_char
> ------------------
> 1,50000000
> (1 row)
>
> # select to_char(1.500, 'FM999999D99999999');
> to_char
> ---------
> 1,5
> (1 row)
>

Maybe its important to add that the nr of decimals in the values
is variable. I could of course adjust the pattern to work for
1.500, but I'm looking for a solution that will work with an
arbitrary numerical value and that's a little more elegant than
casting to text, and then replacing the dot by a comma.

>
> I would like to have '1,500' as the output, what is the best way
> to achieve this?
>
> Thanks in advance,
>
> Ingmar