Re: to_char with locale decimal separator

On Mon, Jul 29, 2013 at 3:45 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> 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)
>

that would work, but that requires keeping track of which decimal
separator to use yourself. If you change the locale, the code
has to change as well. As to_char already converts the decimal
separator in a locale aware manner, I wonder whether there is a
way to do this using the existing locale facilities.

Ingmar