Re: Comparing alphanumeric strings

limaye_at_ccs.neu.edu (Bani Limaye) wrote:
>
>
> How does one do comparisons on character strings that are alphanumeric and
> get the right results?
> For example:
>
> select char_val from y
> where char_val > '711.00'
> and char_val < '911.00'
> order by char_val;
>
> returns (incorrectly):
>
> char_val
> ========
> 711.00A
> 714.00
> 74.00
> 900.00

Why is this incorrect? You are asking for a character string comparision and that is what you are getting. Try putting the following values in any order in a file and invoking the unix sort utility on it:

$ cat xxx; echo ---------; sort xxx

900.00
911.00
714.00
711.00

711.00
711.00A
714.00

As you can see,

711.00A
714.00
74.00
900.00

fall, character-wise, between '711.00' and '911.00' in the above order. I think the problem is in the question you are trying to ask, not the response from the database (which is correct given your question).

Maybe if you let us know what you want for an answer, we can try to get a question that will elicit that response.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Thu May 11 1995 - 00:00:00 CEST