Re: to_char and '=' weirdness

--- 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 statementselect(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