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

Yes, both statements are correct.
I wont explain the numerical first one, everyone knows 3 is not between 10 and 35! ;-)

As for the second, when comparing characters, the character 3 is between 10 and 35. Because you have enclosed your numbers in single quotes, it implicitly gets converted to a character.

Here is an example, try it. (output shortened to fit on page)

  1 create table temp
  2* (numb number)
SQL> / Table created.

SQL> begin
  2 for i in 1 .. 40 loop
  3 insert into temp values (i);
  4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> commit;

SQL> select to_char(numb) from temp order by 1;

1
10
11
..
..
27
28
29
3
30
31
32
33
34
35
36
37
38
39
4
40
5
6
7
8
9

HTH, Mark

aleatory_at_hotmail.com (aleatory) wrote in message news:<a68a4ee0.0201091607.4d1842e_at_posting.google.com>...
> Hi all,
>
> Another newbie question on SQL*Plus.
>
> | SELECT *
> | FROM dual
> | WHERE 3 BETWEEN 10 AND 35
> |
> | no rows selected
>
> I have no problem understanding the previous code.
> Since the number 3 isn't indeed between the numbers
> 10 and 35; therfore, no rows get selected.
>
> | 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.
>
> Thanks in advace.
>
> alea
Received on Thu Jan 10 2002 - 10:08:32 CET