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

Hello Mark, Herbert, and Nail

Thanks all! I finally got it after reading some of the online document as well as you guys' very helpful explanation. I really appreciate them. Thanks!

alea

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3c42cd91$0$225$ed9e5944_at_reading.news.pipex.net>...
> Sorry if I have clouded rather than cleared the issues. What my post was
> trying to say was that enclosing in quotes means do a string comparison not
> compare the ascii value of the character. I therefore chose a number which
> is not used as an ascii code and a meaningless string. In this case the
> where clause will be evaluated along the lines of
>
> does '3' come after '10000' in the string sort order? (which is as hubert
> pointed out language dependent)
> does '3' come before 'BANANA' in the string sort order?
> answer yes to both therefore display all the rows from dual.
>
> my advice would remain represent numbers as numbers and characters as
> characters then the likelihood of a meaningless query such as mine being
> issued is minimal.
>
> hope this helps rather than hinders.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "aleatory" <aleatory_at_hotmail.com> wrote in message
> news:a68a4ee0.0201130252.2cb3ddbf_at_posting.google.com...
> > 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 Fri Jan 18 2002 - 13:10:07 CET