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

[Quoted] Hi Mark and Niall,

[Quoted] 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 - 11:52:41 CET