Is 13 smaller than 2?

[foxroberts]

Hey.

I am at variance with my DB.
My query is:
select id, name from table where number >= '0' and number <= '2'

Now, I get 1,2,13 as the result.
Is there a problem with the first letter?
The type of the number row is varchar. But it's still 13, not 1.3!

I hope you can help me!

Thanks!

[Doug_G]

Since you are looking for '2' you are comparing text values not numbers and the ascii code for '2' is greater than the ascii code for '1', so '13' ends up less than '2'

If MySQL supports it you can CAST your text numerical value to number datatype, then your select should work as you expect since you'll be comparing numbers, not ascii character values.

[Ravenous]

If 'number' is always going to be a number and not a character, why on earth would store it as a character? I would suggest making that column a smallint (tinyint, int.. whatever pleases you.. depends on the number of records you'll be storing). Then your query would look as follows:

select id, name from table where number >= 0 and number <= 2