Hello Roger,

----- Original Message -----
From: "Howard J. Rogers" <hjr@dizwell.com>
Newsgroups: comp.databases.oracle.server
Sent: Monday, April 12, 2004 7:17 PM
Subject: Re: can wild cards be used in BETWEEN clause


> On Mon, 12 Apr 2004 23:27:42 +0100, Andy Hassall <andy@andyh.co.uk> wrote:
>
> >
> > BETWEEN doesn't accept wildcards,
>
> So how come this works?


You are quite right about BETWEEN accepting 'wildcards'. However, the
following statement is incorrect:

<quote>
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.
</quote>

The between predicate implies a closed, not open interval, i.e. an
interval that includes its boundaries as well. You can easily verify this
with numeric data which are probably easier to understand than string data.
As for the example above, it works as it should.

Consider a set of strings like this {'a', 'ab', 'kl', 'z', 'za'}.

The following predicate, BETWEEN 'a%' and 'z%' will select {'ab', 'kl'}
because 'a' < 'a%' so it's not included in the subset; however 'z' < 'z%'
and IS included ( but 'za' > 'z%' and it's skipped). This predicate,
BETWEEN 'a' and 'z', naturally will select {'a', 'ab', 'kl', 'z'}.

VC