CODEWITHSUNDEEP
sqlitecastingboolean
pxp
pxp

Reputation: 87

SQLite Boolean WHERE to check for 0 and NULL value

I'm looking for a SQLite WHERE clause which will give me the records if they are:

0 (false)
NULL (false)
1 (true)

So besides 0 and 1 i also have records with NULL.

I now do a

SELECT * FROM my_table WHERE (MyBoolean IS NULL OR MyBoolean = 0) AND SomeOtherValue NOT NULL;

When using

SELECT * FROM my_table WHERE NOT MyBoolean AND SomeOtherValue NOT NULL;

The value=NULL records are not found, only the value=0 records :o(

Do i have to cast my column as a boolean?

Upvotes: 1

Views: 3037

Answers (3)

pavlovma007
pavlovma007

Reputation: 43

json_quote translate null and false to 0, and true to 1. just use it : json_quote(X)='1'

Upvotes: 0

Dumoko
Dumoko

Reputation: 2644

Handling NULL booleans is really tricky indeed.

I don't know anything about the architecture of your code, but try to consider the following:

Make your MyBoolean NOT NULL DEFAULT TRUE (or FALSE, depends).

It might require minor code refactoring, but it will keep all the SELECTs clean, readable, and will escape you from using hacky methods.

I will repeat myself: this solution might not work if somehow your architecture is very inflexible and you cannot do this sort of refactors.

Hope this helps anybody.

Upvotes: 1

Anton Kovalenko
Anton Kovalenko

Reputation: 21507

Casting won't help here (CAST (null AS sometype) is still NULL). Anything that you can use will be more or less equivalent of the expression you use now: I find WHERE NOT COALESCE(MyBoolean,0) a bit more readable, but that's a personal preference.

Upvotes: 4

Related Questions