Re: problem with to_char

On Tue, Jan 29, 2002 at 01:13:44PM +0000, Thomas Lockhart wrote:
> > I think I have not understood some elementary things of the to_char function
> > in pgsql7.1.2. I only get back a mask instead of a formatted string.
> > I tried the following in psql:
> > SPS=> select to_char(45145, '9.999,99');
> > to_char
> > -----------
> > #.###,##
> > (1 row)
>
> The number will not fit into the field you have defined, so it is filled
> with hash marks to indicate overflow.

Right.

> If your locale is not set up to recognize "european style" numbers, then
> in this case you have defined a formatting string which allows only
> numbers less than 10. If you are set up to recognize european formatting
> (I'm not familiar with this for to_char(); see the docs) then you have
> defined a formatting string with allows only numbers less than 9999,
> which still won't hold the number you are asking it to format for you.
>
> Try to_char(45145, '99,999.99') and to_char(45145, '99.999,99'); one of
> them will work.

Right is to_char(45145, '99,999.99'). The ',' is default (means US)
group separator and the '.' is decimal point. If you want to use
european (means based on locale) formatting you must use 'D' as
deciamal point and 'G' as group separator.

For example:

US style:

test=# select to_char(45145, '99,999.99');
to_char
------------
45,145.00
(1 row)

European (locale) style:

test=# select to_char(45145, '99G999D99');
to_char
------------
45 145,00
(1 row)

The number of '9' front decimal point must be greater or same as is number
of digits in input.

test=# select to_char(45145, '9999');
to_char
---------
####
(1 row)

test=# select to_char(45145, '999999999');
to_char
------------
45145
(1 row)

test=# select to_char(45145, '099999999');
to_char
------------
000045145
(1 row)

test=# select to_char(45145, '099G999G999D999G999');
to_char
----------------------
000 045 145,000 000
(1 row)

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz