Received: from desmpop1.desm.uswest.net (desmpop1.desm.uswest.net [207.108.32.1]) by hub.org (8.9.1/8.9.1) with SMTP id JAA15816 for ; Sun, 10 Jan 1999 09:25:08 -0500 (EST) (envelope-from tim@dmcity.net) Received: (qmail 11510 invoked by alias); 10 Jan 1999 14:25:06 -0000 Delivered-To: fixup-pgsql-sql@hub.org@fixme Received: (qmail 11496 invoked by uid 0); 10 Jan 1999 14:25:05 -0000 Received: from bdsl97.desm.uswest.net (HELO timnt) (207.108.36.97) by desmpop1.desm.uswest.net with SMTP; 10 Jan 1999 14:25:05 -0000 Message-ID: <005c01be3ca4$2abb3e90$61246ccf@timnt.desm.uswest.net> Reply-To: "Tim Perdue, The Des Moines City.net" From: "Tim Perdue, The Des Moines City.net" To: "Bruce Momjian" , Subject: Re: [SQL] SELECT... BETWEEN 'P' and 'Z' Date: Sun, 10 Jan 1999 08:18:59 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 I was using a "text" field, not varchar, but I suppose it treats it the same? And I don't believe there are any significant spaces in the City names. The city that it leaves of out the "Between 'P' and 'Z'" are Zwingle. And it includes "Zwingle" when I do the "between 'P' and 'z'" query. According to the SQL docs that I saw, the "between" command is supposed to be "inclusive", so I'm thinking it's not quite right in pgsql 6.4. But I could be wrong. Tim -----Original Message----- From: Bruce Momjian To: tim@dmcity.net Cc: pgsql-sql@hub.org Date: Sunday, January 10, 1999 12:34 AM Subject: Re: [SQL] SELECT... BETWEEN 'P' and 'Z' >[Charset iso-8859-1 unsupported, filtering to ASCII...] >> What I did was make the Z lowercase, since lowercase Z would be a bigger >> number in the ASCII character set. >> >> So now I'm doing "where x between 'P' and 'z';" >> >> and I get everything that I want. >> > >I think I know your problem. Look at this: > > test=> create table test5 (x varchar(5)); > CREATE > test=> insert into test5 values ('Z '); > INSERT 18698 1 > test=> insert into test5 values ('P'); > INSERT 18699 1 > test=> insert into test5 values ('S'); > INSERT 18700 1 > test=> insert into test5 values ('Z'); > INSERT 18701 1 > test=> select * from test5 where x between 'P' and 'Z'; > x > - > P > S > Z > (3 rows) > >Notice in a varchar field, spaces are significant. This is not true in char(5) >field, so 'Z ' is not between 'P' and 'Z'. We can change this, but I >think this the way it is supposed to work. Using < and > yields the >same result. > > > >-- > Bruce Momjian | http://www.op.net/~candle > maillist@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026