CODEWITHSUNDEEP
databasesqlite
Abhinav
Abhinav

Reputation: 353

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

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

See the picture

Table definition is

enter image description here

Or what is the difference between both null values?

Upvotes: 1

Views: 16872

Answers (2)

sqlab
sqlab

Reputation: 6436

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.

Upvotes: 2

zedfoxus
zedfoxus

Reputation: 37129

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.

Upvotes: 2

Related Questions