SQL BETWEEN command not working for large ranges

Question

The SQL command BETWEEN only works when I give it a small range for column. Here is what I mean:

My code:

import AzureSQLHandler as sql

database_layer = sql.AzureSQLHandler()
RESULTS_TABLE_NAME = "aero2.ResultDataTable"
where_string = " smog BETWEEN '4' AND '9'"

print database_layer.select_data(RESULTS_TABLE_NAME,  "*", where_string)

Which corresponds to SQL command:

SELECT *
FROM aero2.ResultDataTable
BETWEEN '4.0' AND '9.0'

and select_data returns a 2-D array containing all these rows.

The column I am referencing here has already saved all values equal to 5.0.

This Works FINE!

But, when I increase the range to, say, '4.0' AND '200.0', it does not return anything.

Answer 1

Strings in databases are compared alphabetically. A string '4.0' is greater than a string '200.0' because character 4 comes after character 2. You should use numeric type in your database if you need to support this kind of queries. Make sure that smog column has a numeric type (such as DOUBLE) and use BETWEEN 4.0 AND 200.0 in your query.

If you cannot change the schema you can use CAST: cast(smog as DOUBLE) BETWEEN 4.0 and 200.0, however this solution is less efficient.