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.gilb...@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 > -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
