length of return value of to_char()

On May 21, 2011, at 2:00, Seb wrote:

Hi,

I thought this should be equal to 4:

# SELECT length(to_char(11, '0999'));
length
--------
5
(1 row)

but the string returned by the to_char() call is 0011 (length 4). What
am I missing?

There's a preceeding space:

postgres=# SELECT length(to_char(11, '0999'));
length
--------
5
(1 row)

postgres=# SELECT to_char(11, '0999');
to_char
---------
0011
(1 row)

postgres=# SELECT quote_literal(to_char(11, '0999'));
quote_literal
---------------
' 0011'
(1 row)

Compare using Fill Mode (FM):

postgres=# SELECT quote_literal(to_char(11, 'FM0999'));
quote_literal
---------------
'0011'
(1 row)

See the documentation for more details:
<http://www.postgresql.org/docs/9.0/interactive/functions-formatting.html#FUNCTIONS-FORMATTING-NUMERIC-TABLE&gt;

Michael Glaesemann
grzm seespotcode net

On Sat, 21 May 2011 02:12:13 -0400,
Michael Glaesemann <grzm@seespotcode.net> wrote:

[...]

There's a preceeding space:

[...]

Thanks! Good to have quote_literal in mind for this.

--
Seb