Re: can wild cards be used in BETWEEN clause

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 13 Apr 2004 08:26:18 +1000
Message-ID: <opr6c694b03d8uqx@news.optusnet.com.au>

On Tue, 13 Apr 2004 08:18:38 +1000, Howard J. Rogers <hjr_at_dizwell.com> wrote:

> On Mon, 12 Apr 2004 10:58:58 -0400, SAL <2_at_2.com> wrote:
>
>> Hi,
>> I am trying to select names of all countries from a column:
>> SELECT country FROM countries_table
>> WHERE
>> country BETWEEN 'A%' AND 'Z%';
>>
>> This gives me every name except those starting with Z. I wonder if this
>> is
>> the right approach.
>> Any help will be appreciated.
>
>
> It seems to me perfectly logical. Show me everything between A and B
> should not show me B itself, because I've asked for things *between* two
> points, but not the points themselves.
>
> Of course, AZERBAIJAN would get into my list, because it's bigger than
> A, and so is still between A and B.
>
> If you want to see Zimbabwe and Zanzibar, then your 'between' markers
> need to be A at one end, and something like 'ZZ' at the other. ZZ% would
> permit anything up to ZY... to be displayed.
>
> Regards
> HJR
Sorry... I meant to prove the point.

SQL> select * from countries where col1 between 'A%' and 'Z%';

COL1

---

ALGERIA
BOTSWANA
CHAD
DALMATIA
ENGLAND
WESTPHALIA
YUGOSLAVIA 7 rows selected.

SQL> select * from countries where col1 between 'A%' and 'ZZ%';

COL1

---

ALGERIA
BOTSWANA
CHAD
DALMATIA
ENGLAND
WESTPHALIA
YUGOSLAVIA
ZANZIBAR 8 rows selected.

-- 
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
  -A mine of useful Oracle information-
          -Windows Laptop Rac-
    -Oracle Installations on Linux-
===========================================

Received on Mon Apr 12 2004 - 17:26:18 CDT