CODEWITHSUNDEEP
sqlite
Andrej
Andrej

Reputation: 736

SQLite SELECT statement where column equals zero

I'm preety new to SQLite. I have a preety basic question.. Why can't I select rows where specific column equals zero?

The is_unwanted column is type TINYINT (which I see in SQLite basically means INTEGER)

So, I have only one record in the database (for testing). When I try

SELECT is_unwanted FROM 'urls'

I get a result of "0" (zero), which is fine because that column contains the actual number 0.

I tried =>

SELECT * FROM 'urls' WHERE is_unwanted = 0

And got NO result, but

SELECT * FROM 'urls' WHERE is_unwanted <> 0

gives me result.

What am I doing wrong??

Upvotes: 0

Views: 1973

Answers (2)

C&#237;cero Moura
C&#237;cero Moura

Reputation: 2333

You could try:

SELECT * FROM urls WHERE coalesce(is_unwanted,'') = ''

Upvotes: 0

Doug Currie
Doug Currie

Reputation: 41170

Try running

select '{' || is_unwanted || '}' from urls

to see if the value in the database is really a string containing spaces.

SQLite is a dynamically typed database; when you specify TINYINT is is a hint (SQLite uses the term "affinity") for the column. You can use

 select is_unwanted, typeof(is_unwanted) from urls

to see the values with their types.

Upvotes: 2

Related Questions