pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SELECT... BETWEEN 'P' and 'Z'
7+ messages / 3 participants
[nested] [flat]

* SELECT... BETWEEN 'P' and 'Z'
@ 1999-01-09 18:03  Tim Perdue, The Des Moines City.net <tim@dmcity.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Tim Perdue, The Des Moines City.net @ 1999-01-09 18:03 UTC (permalink / raw)
  To: pgsql-sql@hub.org

Hello all,

I am trying to query a zip code database and pull all cities in a state in a
range.

According to the SQL tutorial that the pgsql site recommended, using
"between" in your select should be "inclusive". However, my selects are
including the first letter "p" through "y", and excluding "z".

My SQL:

select distinct on fld_city fld_city, fld_zip from tbl_zip_city where
fld_state = 'IA' and fld_country='us' and (fld_city BETWEEN 'P' AND 'Z')
ORDER BY fld_city ASC;

Any suggestions on how I could get the cities that start with "Z" to be
included here (P is included strangely enough)?

Thanks,

Tim Perdue




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] SELECT... BETWEEN 'P' and 'Z'
@ 1999-01-10 01:37  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Tim Perdue, The Des Moines City.net <tim@dmcity.net>
  0 siblings, 0 replies; 7+ messages in thread

From: Bruce Momjian @ 1999-01-10 01:37 UTC (permalink / raw)
  To: tim@dmcity.net; +Cc: pgsql-sql@hub.org

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Hello all,
> 
> I am trying to query a zip code database and pull all cities in a state in a
> range.
> 
> According to the SQL tutorial that the pgsql site recommended, using
> "between" in your select should be "inclusive". However, my selects are
> including the first letter "p" through "y", and excluding "z".
> 
> My SQL:
> 
> select distinct on fld_city fld_city, fld_zip from tbl_zip_city where
> fld_state = 'IA' and fld_country='us' and (fld_city BETWEEN 'P' AND 'Z')
> ORDER BY fld_city ASC;
> 
> Any suggestions on how I could get the cities that start with "Z" to be
> included here (P is included strangely enough)?

Tried it here:

	test=> create table test5(x char(50));
	CREATE
	test=> insert into test5 values ('P');
	INSERT 18665 1
	test=> insert into test5 values ('Z');
	INSERT 18666 1
	test=> insert into test5 values ('S');
	INSERT 18667 1
	test=> select * from test5 where x between 'P' and 'Z';
	x
	--------------------------------------------------
	P
	Z
	S
	(3 rows)

This is the 6.5 development tree, but should be the same on 6.4.*.

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] SELECT... BETWEEN 'P' and 'Z'
@ 1999-01-10 04:02  Tim Perdue, The Des Moines City.net <tim@dmcity.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Tim Perdue, The Des Moines City.net @ 1999-01-10 04:02 UTC (permalink / raw)
  To: Bruce Momjian <maillist@candle.pha.pa.us>; pgsql-sql@hub.org

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.

--Tim




-----Original Message-----
From: Bruce Momjian <maillist@candle.pha.pa.us>
To: tim@dmcity.net <tim@dmcity.net>
Cc: pgsql-sql@hub.org <pgsql-sql@hub.org>
Date: Saturday, January 09, 1999 7:48 PM
Subject: Re: [SQL] SELECT... BETWEEN 'P' and 'Z'
>
>Tried it here:
>
> test=> create table test5(x char(50));
> CREATE
> test=> insert into test5 values ('P');
> INSERT 18665 1
> test=> insert into test5 values ('Z');
> INSERT 18666 1
> test=> insert into test5 values ('S');
> INSERT 18667 1
> test=> select * from test5 where x between 'P' and 'Z';
> x
> --------------------------------------------------
> P
> Z
> S
> (3 rows)
>
>This is the 6.5 development tree, but should be the same on 6.4.*.
>
>--
>  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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] SELECT... BETWEEN 'P' and 'Z'
@ 1999-01-10 06:29  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Tim Perdue, The Des Moines City.net <tim@dmcity.net>
  0 siblings, 0 replies; 7+ messages in thread

From: Bruce Momjian @ 1999-01-10 06:29 UTC (permalink / raw)
  To: tim@dmcity.net; +Cc: pgsql-sql@hub.org

[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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] SELECT... BETWEEN 'P' and 'Z'
@ 1999-01-10 14:18  Tim Perdue, The Des Moines City.net <tim@dmcity.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Tim Perdue, The Des Moines City.net @ 1999-01-10 14:18 UTC (permalink / raw)
  To: Bruce Momjian <maillist@candle.pha.pa.us>; pgsql-sql@hub.org

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 <maillist@candle.pha.pa.us>
To: tim@dmcity.net <tim@dmcity.net>
Cc: pgsql-sql@hub.org <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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] SELECT... BETWEEN 'P' and 'Z'
@ 1999-01-10 16:43  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Tim Perdue, The Des Moines City.net <tim@dmcity.net>
  0 siblings, 0 replies; 7+ messages in thread

From: Bruce Momjian @ 1999-01-10 16:43 UTC (permalink / raw)
  To: tim@dmcity.net; +Cc: pgsql-sql@hub.org

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 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.

Zwingle is greater than 'Z'.


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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: [SQL] SELECT... BETWEEN 'P' and 'Z'
@ 1999-01-12 19:28  Greg Youngblood <YoungblG@houstoncellular.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Greg Youngblood @ 1999-01-12 19:28 UTC (permalink / raw)
  To: 'Tim Perdue, The Des Moines City.net' <tim@dmcity.net>; pgsql-sql@hub.org

Try this:

select distinct on fld_city fld_city, fld_zip from tbl_zip_city where
fld_state = 'IA' and fld_country = 'us' and (substr(fld_city,1,1) between
'P' and 'Z') order by fld_city asc;

That should look at the first letter and return what your looking for.

Greg

Gregory S. Youngblood
ext 2164

-----Original Message-----
Sent: Saturday, January 09, 1999 12:04 PM
Subject: [SQL] SELECT... BETWEEN 'P' and 'Z'

Hello all,

I am trying to query a zip code database and pull all cities in a state in a
range.

According to the SQL tutorial that the pgsql site recommended, using
"between" in your select should be "inclusive". However, my selects are
including the first letter "p" through "y", and excluding "z".

My SQL:

select distinct on fld_city fld_city, fld_zip from tbl_zip_city where
fld_state = 'IA' and fld_country='us' and (fld_city BETWEEN 'P' AND 'Z')
ORDER BY fld_city ASC;

Any suggestions on how I could get the cities that start with "Z" to be
included here (P is included strangely enough)?



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~1999-01-12 19:28 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-09 18:03 SELECT... BETWEEN 'P' and 'Z' Tim Perdue, The Des Moines City.net <tim@dmcity.net>
1999-01-10 01:37 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-01-10 04:02 Re: [SQL] SELECT... BETWEEN 'P' and 'Z' Tim Perdue, The Des Moines City.net <tim@dmcity.net>
1999-01-10 06:29 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-01-10 14:18 Re: [SQL] SELECT... BETWEEN 'P' and 'Z' Tim Perdue, The Des Moines City.net <tim@dmcity.net>
1999-01-10 16:43 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-01-12 19:28 RE: [SQL] SELECT... BETWEEN 'P' and 'Z' Greg Youngblood <YoungblG@houstoncellular.com>

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