Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: can wild cards be used in BETWEEN clause

Re: can wild cards be used in BETWEEN clause

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Tue, 13 Apr 2004 10:23:56 +0100
Message-ID: <4sbn70hrjrgp1cqjog14jq5ou1evl82i8s@4ax.com>


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%';
>
>COL1
>--------------------
>ALGERIA
>BOTSWANA
>CHAD
>DALMATIA
>ENGLAND
>WESTPHALIA
>YUGOSLAVIA
>ZANZIBAR
>
>And works, moreover, in 8.1.6, 9.2 and 10g.

 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.

 "between 'A%' and 'ZZ%'" matches everything that string sorts between 'A%' and 'ZZ%' inclusive.

 '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
Received on Tue Apr 13 2004 - 04:23:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US