Re: A Question On "WHERE '3' BETWEEN '10' AND '35'" VERSES "WHERE 3 BETWEEN 10 AND 35"
Date: Mon, 14 Jan 2002 12:22:39 -0000
Message-ID: <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...Received on Mon Jan 14 2002 - 13:22:39 CET
> 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