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.gilbert@ec.gc.ca> wrote:

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)

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

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

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. +