Re: length of return value of to_char() - Mailing list pgsql-general

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>

Michael Glaesemann
grzm seespotcode net