pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedto_char and '=' weirdness
5+ messages / 3 participants
[nested] [flat]
* to_char and '=' weirdness
@ 2002-08-08 11:33 Frank Joerdens <frank@joerdens.de>
0 siblings, 1 reply; 5+ messages in thread
From: Frank Joerdens @ 2002-08-08 11:33 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: to_char and '=' weirdness
@ 2002-08-08 11:48 Ludwig Lim <lud_nowhere_man@yahoo.com>
parent: Frank Joerdens <frank@joerdens.de>
0 siblings, 1 reply; 5+ messages in thread
From: Ludwig Lim @ 2002-08-08 11:48 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: to_char and '=' weirdness
@ 2002-08-08 12:11 Karel Zak <zakkr@zf.jcu.cz>
parent: Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Karel Zak @ 2002-08-08 12:11 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: Frank Joerdens <frank@joerdens.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: to_char and '=' weirdness
@ 2002-08-08 12:46 Frank Joerdens <frank@joerdens.de>
parent: Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 1 reply; 5+ messages in thread
From: Frank Joerdens @ 2002-08-08 12:46 UTC (permalink / raw)
To: Karel Zak <zakkr@zf.jcu.cz>; +Cc: Ludwig Lim <lud_nowhere_man@yahoo.com>; Frank Joerdens <frank@joerdens.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: to_char and '=' weirdness
@ 2002-08-08 13:04 Frank Joerdens <frank@joerdens.de>
parent: Frank Joerdens <frank@joerdens.de>
0 siblings, 0 replies; 5+ messages in thread
From: Frank Joerdens @ 2002-08-08 13:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-08-08 13:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-08 11:33 to_char and '=' weirdness Frank Joerdens <frank@joerdens.de>
2002-08-08 11:48 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-08-08 12:11 ` Karel Zak <zakkr@zf.jcu.cz>
2002-08-08 12:46 ` Frank Joerdens <frank@joerdens.de>
2002-08-08 13:04 ` Frank Joerdens <frank@joerdens.de>
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