CODEWITHSUNDEEP
sqlsqlite
S-K'
S-K'

Reputation: 3209

SQLite floating point condition

I am executing the following SQLite query on floating point numbers:

SELECT * FROM Point where x1 < 12 AND x2 > 12

The query works for the less than condition but does not evaluate the greater than condition properly. It seems to think that a value of 6.12 is greater than 12.

Are there any known issues with evaluating floating point numbers in SQLite?

Upvotes: 0

Views: 440

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

Probably, your x2 column is TEXT data. You must CAST it to float in order to make algebraic comparisons:

SELECT * FROM Point where x1 < 12 AND CAST(x2 AS FLOAT) > 12;

Better, would be converting all your string data to floating point:

UPDATE Point SET x2=CAST(x2 AS FLOAT) WHERE CAST(x2 AS FLOAT)=x2;

This will convert all TEXT floating point numbers to FLOAT.

Upvotes: 1

mucio
mucio

Reputation: 7119

Probably you are mixing the conditions, your are on a machine that uses the , as decimal separator or you are comparing strings (as suggested by D Stanley in the comments)

My demo in SQLFiddle works as expected.

Upvotes: 0

Related Questions