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