How to check if a value is not (null) in sqlite database

Question

I want to know how can I match if any value is not null in sqlite database. I am trying with column_name IS NOT NULL but it is not giving me correct result. Actually somewhere it is NULL and somewhere it is (null) in the table. I think it will check for NULL not for (null). How can I get the result which is not (null) ?

Please Have a look on the picture

Table definition is

Or what is the difference between both null values?

`IS NOT NULL` *is* the correct syntax. What are you using to display the values? Are you sure the `Name` column doesn't contain the string `(null)` or that it isn't empty? — Panagiotis Kanavos, Jul 24 '15 at 11:18
@PanagiotisKanavos yeah I am not passing anything to this column while saving. If I am not passing anything that means it should be `NULL` at least. But the entry is saving as `(null)`. Don't know why. But you know when I am using this condition `Name != (null)` then it is not giving me anything. — Abhinav, Jul 24 '15 at 11:20
How are you saving the data? Which SQLite version are you using and which environment? [This similar question](http://stackoverflow.com/questions/7519621/where-is-null-not-working-in-sqlite) shows that there may be quirks from one environment to the other. — Panagiotis Kanavos, Jul 24 '15 at 11:29
Also, what is your table's definition? Is the column defined as NULL or NOT NULL? — Panagiotis Kanavos, Jul 24 '15 at 11:31
It seems you have some fields, probably char or varchar, where the literal value is "(null)" — sqlab, Jul 24 '15 at 11:34
@PanagiotisKanavos I have edited my question. Added screenshot for definition. Actually I did not defined anything explicitly. Whatever is defined that is by default. — Abhinav, Jul 25 '15 at 03:07
@sqlab Can you have a look over the definition of table. And also it's working fine if I am using `Name != '(null)'` But why is it so? That I am not able to understand. — Abhinav, Jul 25 '15 at 03:09

zedfoxus · Answer 1 · 2015-07-25T05:04:21.060

You'd match a not null column with IS NOT NULL keywords.

The (null) you see is just how your editor is indicating that the column is devoid of value.

Take this SQLite (WebSQL) SQLFiddle for example: http://sqlfiddle.com/#!7/178db/3

create table test (
  field1 int,
  field2 int
);

insert into test (field1) values (1);
insert into test (field1) values (2);

-- below statement will result in no results
select * from test where field2 is not null;

-- below statement will result in 2 records
select * from test where field2 is null;

field1  field2
------  ------
     1  (null)
     2  (null)

Testing on Mac

Open Terminal in Mac and type the following commands.

$> sqlite testfile.sqlite.db
sqlite> select * from testing;
sqlite> insert into testing (field1) values (1);
sqlite> insert into testing (field1) values (2);

-- Notice the results
sqlite> select * from testing;
1|
2|

-- Notice the results when requesting field2 is null
sqlite> select * from testing where field2 is null;
1|
2|

-- Notice the results when requesting field2 is NOT null
sqlite> select * from testing where field2 is not null;
sqlite> .quit

Now, go to the directory in which your SQLite file sits

$> cd /Users/<you>/Documents
$> sqlite <myfile.db>

-- retrieve your table's CREATE TABLE statement 
-- and add it to your answer
$> .dump ServiceObjects

-- check to see if your NULL values are appearing similar to the example above
-- if feasible, paste a portion of your output in your answer as well
$> select * from ServiceObjects

-- try running queries with WHERE <field-of-interest> IS NOT NULL
-- try running queries with WHERE <field-of-interest> IS NULL

Are you getting results similar to the results I see? Please also include your SQLite version in your edited answer. I am using 3.8.10.2.

Yes that is working in your example perfectly. But don't know why it is not working perfectly in my system. Anyway right now I am comparing like string `Name != '(null)'` that is working for me but not having any result why is it so. May be that is saved is varchar. — Abhinav, Jul 25 '15 at 04:02
OS doesn't matter to SQLite. My response matters. I'll add to my answer a method to do testing on Mac. — zedfoxus, Jul 25 '15 at 04:54

score 2 · Accepted Answer · edited Jul 05 '18 at 07:55

from sqlite datatype

Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".

A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible.

So somewhere along the line this columns where written with the literal value '(null)'. And this values can e.g. not converted to INTEGER witout loss.

I recommend to update your table(s) with

UPDATE yourtable SET name IS NULL WHERE name = '(null)';
UPDATE yourtable SET desc IS NULL WHERE desc = '(null)';
UPDATE ... a.s.o

for all relevant tables and columns, so that you get consistent tables.

How to check if a value is not (null) in sqlite database

2 Answers2