Re: A Question On "WHERE '3' BETWEEN '10' AND '35'" VERSES "WHERE 3 BETWEEN 10 AND 35"

Hi Alea

dont get confused about this.
It simply depends on (language dependent) sorting conventions (NLS_SORT, NLS_LANG) eg:
are uppercase letters sorted before or after lowercase, are all lowercase letters sortet first, and then all the uppercase, are letters sorted before or after numeric characters ... for me, I want to get the german umlauts sorted with the letter, eg. a, A, ä, Ä

The sorting with numeric charctes would work like expected with the following sql:

   1 SELECT *
   2 FROM dual
   3 WHERE '03' BETWEEN '10' AND '35' no rows selected

aleatory wrote:

> Hi Mark and Niall,
>
> I though I uderstood the problem after reading the post by
> Mark; however, I'm totally puzzled now because of the following
> code:
>
> > SQL> ed
> > Wrote file afiedt.buf
> >
> > 1 SELECT *
> > 2 FROM dual
> > 3* WHERE '3' BETWEEN '10000' AND 'BANANA'
> > 4 /
> >
> > D
> > -
> > X
>
> How does the Oracle SQL*Plus evaluate the WHERE clause in this
> example? Could you explain a bit more on this? I'm totally
> confused...
>
> Thanks in advance!
>
> alea
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3c3ec800$0$230$ed9e5944_at_reading.news.pipex.net>...
> > "aleatory" <aleatory_at_hotmail.com> wrote in message
> > news:a68a4ee0.0201091607.4d1842e_at_posting.google.com...
> > > Hi all,
> > >
> > > Another newbie question on SQL*Plus.
> > <snip>
> > > | SELECT *
> > > | FROM dual
> > > | WHERE '3' BETWEEN '10' AND '35'
> > > |
> > > | D
> > > | -
> > > | X
> > >
> > > I have no idea why SQL*Plus thinks the previous "WHERE"
> > > clause to be true. How does SQL*Plus evaluate the "WHERE"
> > > clause? If I were an SQL*Plus program, I would intuitively
> > > deduce, "Is the ASCII value '3' between the ASCII value '10'
> > > and another ASCII value of '35'? Then return no rows. Just
> > > looking at the result, however, my thinking is totally
> > > incorrect!
> > > Would appreciate any helpful comment on this.
> >
> > You are not comparing ascii values you are comparing strings.
> >
> > SQL> ed
> > Wrote file afiedt.buf
> >
> > 1 SELECT *
> > 2 FROM dual
> > 3* WHERE '3' BETWEEN '10000' AND 'BANANA'
> > 4 /
> >
> > D
> > -
> > X
> >
> > SQL> ed
> > Wrote file afiedt.buf
> >
> > 1 SELECT *
> > 2 FROM dual
> > 3* WHERE chr(3) BETWEEN chr(10) AND chr(35)
> > SQL> /
> >
> > no rows selected
Received on Sun Jan 13 2002 - 13:42:19 CET