Thread: SELECT... BETWEEN 'P' and 'Z'

SELECT... BETWEEN 'P' and 'Z'

From

"Tim Perdue, The Des Moines City.net"

Date:

09 January 1999, 16:10:05

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

Re: [SQL] SELECT... BETWEEN 'P' and 'Z'

From

Bruce Momjian

Date:

09 January 1999, 23:37:29

[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