Thread: to_char with locale decimal separator
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?
Thanks in advance,
Ingmar
On Mon, Jul 29, 2013 at 1:24 PM, Ingmar Brouns <swingi@gmail.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
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.klaver@gmail.com
On Mon, Jul 29, 2013 at 3:45 PM, Adrian Klaver <adrian.klaver@gmail.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
On 07/29/2013 07:27 AM, Ingmar Brouns wrote: > On Mon, Jul 29, 2013 at 3:45 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: >> On 07/29/2013 04:24 AM, Ingmar Brouns wrote: >>> >>> Hi, >> >> 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. Sorry, I just got to your second post where you explained that. I can't think of a way at the moment. > > Ingmar > > -- Adrian Klaver adrian.klaver@gmail.com
On Mon, Jul 29, 2013 at 3:12 PM, Ingmar Brouns <swingi@gmail.com> wrote: > On Mon, Jul 29, 2013 at 1:24 PM, Ingmar Brouns <swingi@gmail.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. > anyone? Giving a locale corresponding textual representation of a numerical value keeping the exact nr of decimal digits must be a fairly common use case. Would it be an idea to implement a to_char function that does not take a formatting pattern and has this behaviour? > >> >> I would like to have '1,500' as the output, what is the best way >> to achieve this? >> >> Thanks in advance, >> >> Ingmar
On 07/30/2013 03:03 AM, Ingmar Brouns wrote:
> On Mon, Jul 29, 2013 at 3:12 PM, Ingmar Brouns <swingi@gmail.com> wrote:
>>
>
> anyone? Giving a locale corresponding textual representation
> of a numerical value keeping the exact nr of decimal digits
> must be a fairly common use case. Would it be an idea to
> implement a to_char function that does not take a formatting
> pattern and has this behaviour?
>
Best I can do is a proof of concept in plpythonu for determining locale
decimal point:
test=# SHOW lc_numeric ;
lc_numeric
-------------
en_US.UTF-8
(1 row)
test=# DO $$
import locale
rs = plpy.execute("SHOW lc_numeric")
lc_n = rs[0]["lc_numeric"]
locale.setlocale(locale.LC_NUMERIC, lc_n)
d = locale.nl_langinfo(locale.RADIXCHAR)
plpy.notice("Decimal point is " + d)
$$ LANGUAGE plpythonu;
NOTICE: Decimal point is .
CONTEXT: PL/Python anonymous code block
DO
test=# set lc_numeric = 'nl_NL.utf8';
SET
test=# DO $$
import locale
rs = plpy.execute("SHOW lc_numeric")
lc_n = rs[0]["lc_numeric"]
locale.setlocale(locale.LC_NUMERIC, lc_n)
d = locale.nl_langinfo(locale.RADIXCHAR)
plpy.notice("Decimal point is " + d)
$$ LANGUAGE plpythonu;
NOTICE: Decimal point is ,
CONTEXT: PL/Python anonymous code block
DO
>>
>>>
>>> I would like to have '1,500' as the output, what is the best way
>>> to achieve this?
>>>
>>> Thanks in advance,
>>>
>>> Ingmar
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
On Tue, Jul 30, 2013 at 4:42 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On 07/30/2013 03:03 AM, Ingmar Brouns wrote:
>>
>> On Mon, Jul 29, 2013 at 3:12 PM, Ingmar Brouns <swingi@gmail.com> wrote:
>
>
>>>
>>
>> anyone? Giving a locale corresponding textual representation
>> of a numerical value keeping the exact nr of decimal digits
>> must be a fairly common use case. Would it be an idea to
>> implement a to_char function that does not take a formatting
>> pattern and has this behaviour?
>>
>
> Best I can do is a proof of concept in plpythonu for determining locale
> decimal point:
>
> test=# SHOW lc_numeric ;
> lc_numeric
> -------------
> en_US.UTF-8
> (1 row)
>
> test=# DO $$
> import locale
> rs = plpy.execute("SHOW lc_numeric")
> lc_n = rs[0]["lc_numeric"]
> locale.setlocale(locale.LC_NUMERIC, lc_n)
> d = locale.nl_langinfo(locale.RADIXCHAR)
> plpy.notice("Decimal point is " + d)
> $$ LANGUAGE plpythonu;
> NOTICE: Decimal point is .
> CONTEXT: PL/Python anonymous code block
> DO
>
>
> test=# set lc_numeric = 'nl_NL.utf8';
> SET
> test=# DO $$
> import locale
> rs = plpy.execute("SHOW lc_numeric")
> lc_n = rs[0]["lc_numeric"]
> locale.setlocale(locale.LC_NUMERIC, lc_n)
> d = locale.nl_langinfo(locale.RADIXCHAR)
> plpy.notice("Decimal point is " + d)
> $$ LANGUAGE plpythonu;
> NOTICE: Decimal point is ,
> CONTEXT: PL/Python anonymous code block
> DO
>
Thanks for your time, appreciate it! As a dirty alternative, you could also do
something like:
select translate(5.000::text,'.',substr(to_char(.0),2,1));
Not so nice, but would work. Though I still feel there should be a more
elegant of doing this...
>
>
>>>
>>>>
>>>> I would like to have '1,500' as the output, what is the best way
>>>> to achieve this?
>>>>
>>>> Thanks in advance,
>>>>
>>>> Ingmar
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
On 07/30/2013 08:34 AM, Ingmar Brouns wrote: > > Thanks for your time, appreciate it! As a dirty alternative, you could also do > something like: > > select translate(5.000::text,'.',substr(to_char(.0),2,1)); > > Not so nice, but would work. Though I still feel there should be a more > elegant of doing this... > Two issues. 1) Leading and trailing zeros tend to be a personal preference and what and how many, even more so. Pre-canned implementations for formatting seem to best guess. I could see where trying to cover every conceivable possibility would get complex. 2) You where looking at converting variable precision values, not uncommon, but adds a layer of complexity. For instance using the Python locale module it is possible to have it format a number according to locale and have trailing zeros, but it will pad to the specified precision(6 by default). So you still need to track something, in this case precision. You could take your quick and dirty solution and put it in a function(to_char_trailing(numeric) and hide the dirty part:) >> -- >> Adrian Klaver >> adrian.klaver@gmail.com -- Adrian Klaver adrian.klaver@gmail.com