I don't know if there is a standard for treating empty character strings as if they were not-present values without first applying one of the builtin functions designed to deal with NULL. However, my opinion is that SQLite is correct and Oracle is wrong.
NULL has a very specific meaning. It means is undefined or unknown. A NULL is not equal to any other value, even another NULL. Nor is a NULL not equal to any value, even another NULL. In fact, any operation where one of the terms is NULL should always return a NULL. Aggregates should ignore NULL. You test for a null value using IS NULL and IS NOT NULL. This is how SQLite behaves, and is correct. An empty string is a value. It is less than all other non-zero length strings and equal to other zero length strings. It is not NULL because it is a defined value. '' IS NULL is FALSE, and '' IS NOT NULL is TRUE. There is a function for turning a value into a null, NULLIF; and, for converting a null to a value, COALESCE. These are standard in pretty much all SQL implementations, including SQLite: https://www.sqlite.org/lang_corefunc.html So you can solve the issue by doing this where you want empty strings in Year2012 treated as NULL's: Select count(nullif(Year2012,'')) from members; --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Black, Michael (IS) > Sent: Monday, 17 September, 2012 08:50 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Count(*) help > > Or just fix the existing table: > > update members set year2007=NULL where year2007=''; > update members set year2008=NULL where year2008=''; > update members set year2009=NULL where year2009=''; > update members set year2010=NULL where year2010=''; > update members set year2011=NULL where year2011=''; > update members set year2012=NULL where year2012=''; > > Then the counts should be what you want. > > I'll note that Oracle doesn't count ''. whereas sqlite3 does. Does the SQL > standard say anything about what a "NULL" value is? And who's correct here > if there is a standard? > > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Advanced GEOINT Solutions Operating Unit > Northrop Grumman Information Systems > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
