Обсуждение: to_char and '=' weirdness

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

select * from adressen where to_char(plz, '00000') = '04109';

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?

Regards, Frank

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

On Thu, Aug 08, 2002 at 04:48:24AM -0700, Ludwig Lim wrote:
> 
> > 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)
Right.

>   try using :
>    
>   select to_char(plz,'00000')
>   from andresses 
>   where trim(to_char(plz,'00000'))='04109'
use FM instead trim(): to_char(plz,'FM00000')
...and please, better read  docs :-)
   Karel

-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz

On Thu, Aug 08, 2002 at 02:11:55PM +0200, Karel Zak wrote:
> On Thu, Aug 08, 2002 at 04:48:24AM -0700, Ludwig Lim wrote:
> > 
> > > 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)
> 
>  Right.
> 
> >   try using :
> >    
> >   select to_char(plz,'00000')
> >   from andresses 
> >   where trim(to_char(plz,'00000'))='04109'
> 
>  use FM instead trim(): to_char(plz,'FM00000')
> 
>  ...and please, better read  docs :-)

Well, I just looked again and couldn't find it. Mostly I use Bruce's
excellent book (pdf version from November 2000), which at least mentions
to_char. I just went to

http://www.de.postgresql.org/users-lounge/docs/7.2/postgres/

and looked under data types and type conversion, to no avail.

Where is this documented?

Regards, Frank

On Thu, Aug 08, 2002 at 02:46:47PM +0200, Frank Joerdens wrote:
[ . . . ]
> >  ...and please, better read  docs :-)
> 
> Well, I just looked again and couldn't find it.

With a little help from Google, I did manage to find it:

http://www.de.postgresql.org/users-lounge/docs/7.2/postgres/functions-formatting.html

Sorry for being a little dense.

Cheers, Frank