Re: to_char and '=' weirdness

--- Frank Joerdens <frank(at)joerdens(dot)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