CODEWITHSUNDEEP
sqlsqliteselecttype-conversion
King King
King King

Reputation: 63317

Cast() doesn't work properly in SQLite?

I have a column of image, I want to add a select column (called HasImage) to indicate that if the image is null, the HasImage should have value of false (or bit 0), otherwise the HasImage should have value of true (or bit 1):

SELECT CAST(CASE WHEN ImageColumn IS NULL THEN 0 ELSE 1 END AS bit) AS HasImage FROM MyTable

I know of IsNull function, but I had just tried this before using IsNull, when filling the data with the above query into a DataTable, the values are all of long type (not bool as I expected)?

Any idea on why it is like that?

Thank you very much in advance!

Upvotes: 0

Views: 1850

Answers (2)

mvp
mvp

Reputation: 116127

Simply use this:

SELECT CASE WHEN ImageColumn IS NOT NULL THEN 1 ELSE 0 END AS HasImage
FROM mytable

Note that SQLite does not have true BOOLEAN or BIT type. Instead, you can use resultant integer value 0 or 1 as if it was boolean - it typically works great for all intents and purposes, including using it in boolean expressions.

As an example, see this SQLFiddle.

Upvotes: 2

BellevueBob
BellevueBob

Reputation: 9618

I don't think SQLite has a "bit" data type. See this: http://www.sqlite.org/datatype3.html

Upvotes: 0

Related Questions