CODEWITHSUNDEEP
sqlsqlite
Hatem Said
Hatem Said

Reputation: 353

blank values returns when using greater than operator in SQlite

select cars.car_id, cars.doors from cars where cars.doors>2

the previous query returns results greater than 2 in addition to empty values and 'N/A' , I tried NOT NULL but it doesn't make sense that blank is greater than 2

Upvotes: 1

Views: 914

Answers (3)

CL.
CL.

Reputation: 180080

The documentation says:

An INTEGER or REAL value is less than any TEXT or BLOB value.

To prevent empty entries from matching, you should change the database to use SQL's NULL value, not an (empty or non-empty) string.

Alternatively, convert your values that mean "empty" to an actual NULL value while querying:

where nullif(cars.doors, 'N/A') > 2

Upvotes: 1

Hatem Said
Hatem Said

Reputation: 353

select doors from cars where cast(doors AS integer)>2;

it works as expected but i still don't get it why i should have converted it to integer although the field type is integer

Upvotes: 0

LONG
LONG

Reputation: 4610

It is the implicit conversion, when you have the empty data, doing the comparison, it will be converted to 0.

select case when '' = 0 then 1 else 0 end

The result is 1.

Upvotes: 0

Related Questions