Thread: to_char bug?

Is there any reason why :

SELECT char_length(to_char(1, '000'));

Gives a result

 char_length
-------------
           4
(1 row)

It seems that to_char(1, '000') gives a string " 001" with a space in front.
Is this a bug?

Regards,

Ben

Ben Trewern wrote:
> Is there any reason why :
>
> SELECT char_length(to_char(1, '000'));
>
> Gives a result
>
>  char_length
> -------------
>            4
> (1 row)
>
> It seems that to_char(1, '000') gives a string " 001" with a space in front.
> Is this a bug?
>
> Regards,
>
> Ben
>
Try formatting the result:
SELECT char_length(to_char(1, 'fm000'));

char_length
-------------
            3

"Ben Trewern" <bendottrewern@mowlem.com> writes:
> It seems that to_char(1, '000') gives a string " 001" with a space in front.
> Is this a bug?

No.

Possibly you want 'FM000'.

            regards, tom lane

From the docs:

"FM suppresses leading zeroes and trailing blanks that would otherwise be
added to make the output of a pattern be fixed-width"

It works now but for one I don't understand why the space is added in the
firs place and two I think the docs don't tell the whole story ie leading
blanks and I assume trailing zeros if applicable.

Regards,

Ben

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:794.1109627049@sss.pgh.pa.us...
> "Ben Trewern" <bendottrewern@mowlem.com> writes:
>> It seems that to_char(1, '000') gives a string " 001" with a space in
>> front.
>> Is this a bug?
>
> No.
>
> Possibly you want 'FM000'.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

If the number is negative there needs to be room for the minus sign...

On Tue, Mar 01, 2005 at 09:25:02AM -0000, Ben Trewern wrote:
> >From the docs:
>
> "FM suppresses leading zeroes and trailing blanks that would otherwise be
> added to make the output of a pattern be fixed-width"
>
> It works now but for one I don't understand why the space is added in the
> firs place and two I think the docs don't tell the whole story ie leading
> blanks and I assume trailing zeros if applicable.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Thanks,  sometimes the obvious just passes me by. :-(

>If the number is negative there needs to be room for the minus sign...

"Martijn van Oosterhout" <kleptog@svana.org> wrote in message
news:20050302103212.GA4567@svana.org...