From: Ludwig Lim <lud_nowhere_man@yahoo.com>
To: Frank Joerdens <frank@joerdens.de>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: to_char and '=' weirdness
Date: Thu, 8 Aug 2002 04:48:24 -0700 (PDT)
Message-ID: <20020808114824.4497.qmail@web40018.mail.yahoo.com> (raw)
In-Reply-To: <20020808133300.A15630@superfly.archi-me-des.de>
--- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lud_nowhere_man@yahoo.com, frank@joerdens.de
Subject: Re: to_char and '=' weirdness
In-Reply-To: <20020808114824.4497.qmail@web40018.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox