Re: Extra space when converting number with to_char

Thank you, it works. The documentation gave me the impression that the FM
modifier only applied to date/time since it was under "Usage notes for
date/time formatting:"

Samuel

On Friday, October 26, 2012 16:01:08 Moshe Jacobson wrote:
> You want to use a format of 'FM000000000' (fill mode, doesn't leave an
> extra space for sign)
>
> On Fri, Oct 26, 2012 at 2:50 PM, Samuel Gilbert
<samuel(dot)gilbert(at)ec(dot)gc(dot)ca>wrote:
> > Hello,
> >
> > I've encountered an unexpected behavior when converting a number to
> >
> > char. I
> > need to match an INTEGER with a VARCHAR(9) that has leading zeros. When
> > I tried to match them using to_char(num, '000000000') I didn't get
> > anything.
> >
> > I
> >
> > later found out that to_char adds a leading space. This is demonstrated
> > with
> > the query below :
> >
> > SELECT
> >
> > string,
> > length(string),
> > '"' || string || '"' AS showThemToMe
> >
> > FROM
> >
> > (SELECT to_char(42, '000000000') AS string) AS example;
> >
> > string | length | showthemtome
> >
> > ------------+--------+--------------
> >
> > 000000042 | 10 | " 000000042"
> >
> > Is there a reason why to_char adds a leading space? Is this a bug? I
> > can easily fix this with trim(leading ' ' from to_char(num,
> > '000000000')), but, being of a curious nature, I'd like ton know why I
> > need to do that.
> >
> > --
> > Samuel Gilbert
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general

--
Samuel Gilbert
Programmeur Analyste
Section applications en modélisation de la qualité de l'air
Division des opérations des prévisions nationales
Environnement Canada
2121, Autoroute Transcanadienne
Dorval (Québec) H9P 1J3