Re: Extra space when converting number with to_char

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
>

--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com