Re: can wild cards be used in BETWEEN clause

On Tue, 13 Apr 2004 10:23:56 +0100, Andy Hassall <andy_at_andyh.co.uk> wrote:

> On Tue, 13 Apr 2004 09:17:01 +1000, "Howard J. Rogers" <hjr_at_dizwell.com>
> wrote:
>
>>> BETWEEN doesn't accept wildcards,
>>
>> So how come this works?
>>
>> SQL> select * from countries where col1 between 'A%' and 'ZZ%';

>
> It's just the usual dictionary sorting of strings. ZANZIBAR < ZZ% for
> the same
> reason ZANZIBAR < ZZA. '%' isn't a wildcard here, it's just another
> character.
>
> 'ZZ&' would be the next string that wouldn't match, as '&' is one
> character up
> from % (in most charsets anyway). Or 'ZZ&!' may be the 'next' string that
> doesn't match depending on how you define next.
>
> Since '%' is below the alphanumerics in most charsets, it's pretty much
> redundant here anyway - 'ZZ' would have done just as well and not have
> looked
> like a wildcard. But would fail for some mythical country 'ZZZLAND' as
> you
> mention in a previous post.
>
> --
> Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
> http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

OK, I'll buy it.

You're quite right, and it makes sense.

Regards and thanks
HJR

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