If values were always numeric, there would be no difference between
these statements.
On the other, if there are others alphabetical characters:
between operator checks on the binary code of the characters.
For example, for a value on one character:
* value between 'a' and 'c' checks for character with code
between 97 and 99 and returns values equal to 'a', 'b' or 'c'
* value between upper('a') and upper('c') checks for character with
code between 65 and 67 and returns values equal to 'A', 'B' or 'C'
* upper(value) between 'a' and 'c' never returns something
because upper function never returns a code with between
97 and 99
* upper(value) between upper('a') and upper('c') checks for
value which code returns by the upper function is between
65 and 67 that is 'a', 'b', 'c', 'A', 'B' or 'C'
Martin Douglas a écrit dans le message <37CD9B98.4A2E301F_at_Boeing.com>...
>Just when I thought I knew a lot...
>
>Why are the following not equivalent???
>
>select ... where upper(a) between upper(b) and upper(c);
>
>select ... where upper(a) between b and c;
>
>select ... where a between b and c;
>
>The variables a,b,c are varchar2's and usually are numeric, though not
>always. This is desired for the current application.
>
>Why does the first one not work?
>
>Thanks in advance!
>
>Martin Douglas
Received on Thu Sep 02 1999 - 03:55:26 CDT
