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?

score 8 · Accepted Answer · answered Mar 12 '10 at 16:49

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'.

1 Answers1