Thread: Extra space when converting number with to_char

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

Placeholder for a +/- sign so that both positive and negative values have equal lengths.

David J.

On Oct 26, 2012, at 14:50, Samuel Gilbert <samuel.gilbert@ec.gc.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On 10/26/12 11:50, Samuel Gilbert wrote:
>
>    (SELECT to_char(42, '000000000') AS string) AS example;
>
>    string   | length | showthemtome
> ------------+--------+--------------
>   000000042 |     10 | " 000000042"

Sign padding.  Try using:
   select to_char(42, 'FM000000000') ...

To see why, look here:
http://www.postgresql.org/docs/9.2/static/functions-formatting.html#FUNCTIONS-FORMATTING-NUMERICMOD-TABLE

HTH

Bosco.

You want to use a format of 'FM000000000' (fill mode, doesn't leave an extra space for sign)



--

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.gilbert@ec.gc.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@postgresql.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

On Fri, Oct 26, 2012 at 04:11:42PM -0400, Samuel Gilbert wrote:
> 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:"

Uh, I see:

        <entry><literal>FM</literal> prefix</entry>
        <entry>fill mode (suppress padding blanks and trailing zeroes)</entry>
        <entry><literal>FM9999</literal></entry>

Does that need clarification?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +