BETWEEN SQL command does not work for large ranges

Question

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

My code is:

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 the SQL command:

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

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

In the column shown here, all values ​​equal to 5.0 are already stored.

It works FINE!

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

Answer 1

Database rows are compared alphabetically. Line '4.0' larger than line '200.0' because character 4 appears after character 2 . You must use a numeric type in your database if you need to support such queries. Make sure the smog column is a numeric type (e.g. DOUBLE) and uses 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.