Difference between varchar and int

[pradeepjain]

Hi,
I was storing integer values like 5000 and 10000 in a column which was defined as varchar.

i was trying to use statement like

1.

select * from mytable where mycol between '5000' and '10000';

but was not working but
2.

 select * from mytable where mycol between 5000 and 10000;

but i changed the datatype to int and it started working . 1st statement started working,just confused

Just wanted to know how ' ' was making difference in the statement

[Atli]

Hey.

Anything between single-quotes is considered a string, not a number. When you say '500', MySQL reads that as three characters of text, but when you say 500, MySQL reads that as a single number.

The BETWEEN ... AND operators are meant to check if a value is in between a range of numbers (dates are also considered numbers internally). Trying to use it on strings will not work properly.

I was storing integer values like 5000 and 10000 in a column which was defined as varchar.

This is a big mistake, by the way. A huge waste of space. - When you store an integer as a VARCHAR, the storage requirements for each letter in the number is equal to the total storage requirements if it were stored as an INT.

And that's not to mention the headaces it will give you when you try to actually use it as a number (as you have discovered).

[pradeepjain]

but
when i make the column as int and use



1.

 select * from mytable where mycol between '5000' and '10000';

it works. how is it ?

[Atli]

MySQL must assume the values are supposed to be the same type as the "mycol" column and silently converts them. - It does the same when you try to compare an INT to a string.

However, you should try not to quote values that are supposed to be numbers. Even if MySQL does automatically convert some of them, it costs a few cycles and the behavior may not be consistent.

Bottom line: always leave numbers unquoted, and only quote strings and dates.

[pradeepjain]

Thanks a lot for help.Its one of the guidlines for me.