Re: Extra space when converting number with to_char

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(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