Re: to_char with locale decimal separator

On 07/29/2013 04:24 AM, Ingmar Brouns 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)
>
>
> I would like to have '1,500' as the output, what is the best way
> to achieve this?

This work?:

test=> select replace(1.500::text, '.', ',');
replace
---------
1,500
(1 row)

>
> Thanks in advance,
>
> Ingmar
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com