MySql query BETWEEN two Strings sometimes doesn't work

Question

I have a mysql query where a subquery is:

BETWEEN '5.00' AND '10.00' 

And this returns no results.

However when I use Floats or Ints:

BETWEEN 5 and 10 

it works

The BETWEEN query also works for other values AS strings, but just not for 5 and 10.

EG:

BETWEEN '4' AND '5' 

works.

How could this be?

Answer 1

There is a difference between strings and integers -- there is a reason why you have several different datatypes :

When comparing integers, you are comparing their numerical values ; i.e. 10 is bigger than 5.

When comparing strings, you are using an alphabetical comparison ; i.e. '10' is smaller than '5', because '1' is smaller than '5'.