Subject Re: [firebird-support] Help with strange answer in WHERE clause
Author Alexandre Benson Smith
hcarvajalsy wrote:

>Hello,
>I am having a strange behaviour in a SELECT statement. The field
>over which I do the WHERE is defined as VARCHAR(5). If I do:
>SELECT COUNT(*) FROM STUDENTS WHERE SCHOOL <= '999';
>
>I get 10,569 as the answer, but if I do:
>SELECT COUNT(*) FROM STUDENTS WHERE SCHOOL <= '1000';
>
>I get 0 as the answer.
>Thanks.
>
>
Helo,

If field is a varchar or char you are comparing string values not
integer values:

in alfabetical order '1000' is less than '999' since '1' comes first
then '9'

try to cast the value to integer if you wish or store your values left
padded with 0s like
'0999'
'0001'
'1000'

Anyway why store a char value if you want to compare it as numbers ?

See you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br