Re: can wild cards be used in BETWEEN clause

Hello Roger,

• Original Message ----- From: "Howard J. Rogers" <hjr_at_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_at_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 Received on Mon Apr 12 2004 - 19:43:42 CDT