Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 04D47475498
	for <pgsql-sql@postgresql.org>; Thu,  8 Aug 2002 07:48:22 -0400 (EDT)
Received: from web40018.mail.yahoo.com (web40018.mail.yahoo.com
 [66.218.78.58])
	by postgresql.org (Postfix) with SMTP id 4E01447546C
	for <pgsql-sql@postgresql.org>; Thu,  8 Aug 2002 07:48:21 -0400 (EDT)
Message-ID: <20020808114824.4497.qmail@web40018.mail.yahoo.com>
Received: from [203.87.150.114] by web40018.mail.yahoo.com via HTTP;
	Thu, 08 Aug 2002 04:48:24 PDT
Date: Thu, 8 Aug 2002 04:48:24 -0700 (PDT)
From: Ludwig Lim <lud_nowhere_man@yahoo.com>
Subject: Re: to_char and '=' weirdness
To: Frank Joerdens <frank@joerdens.de>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
In-Reply-To: <20020808133300.A15630@superfly.archi-me-des.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200208/145
X-Sequence-Number: 8991


--- Frank Joerdens <frank@joerdens.de> wrote:
> German area codes from the former east all have a
> leading zero. As I
> store area codes as integers, I use to_char to find
> those with a leading
> zero, as in
> 
> select * from adressen where to_char(plz, '00000')
> like '%04109%';
> 
> (this would be Leipzig)
> 
> What I don't understand is why something like
> 
> C
> 
> will yield 0 rows. If I do
> 
> archi=# select to_char(plz, '00000') from adressen
> where
> archi-# to_char(plz, '00000') like '%04109%';
> 
> I get:
> 
>  to_char
> ---------
>   04109
>   04109
>   04109
>   04109
>   04109
>   04109
>   04109
>   04109
>   04109
>   04109
>   04109
>   04109
>   04109
>   04109
>   04109
> (15 rows)
> 
> (i.e. my database contains 15 addresses from
> Leipzig)
> 
> which would appear to indicate that to_char actually
> yields the string
> '04109'. Why doesn't the '=' operator work then?

  I tried running a test sql statement
 select(length(to_char(99,'00000')))
 It return six.

 I think that postgreSQL put a leading blank to
indicate that it is a positive number (not sure, just
my opinion)

  try using :
   
  select to_char(plz,'00000')
  from andresses 
  where trim(to_char(plz,'00000'))='04109'


ludwig

__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com