Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: can wild cards be used in BETWEEN clause
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/spaceReceived on Tue Apr 13 2004 - 04:23:56 CDT