On 13 Aug 2011, at 4:50pm, matthew (matthew.jsoft) white wrote: > I'm not too clear about your answer. Could you please elaborate? > > Your response about zero length strings certainly helps with my understanding > of the output of select statements, in that when you see a blank field it is > probably a string with no content.
Right. In an app you can use 'typeof()' to test. In the shell you can make this apparent in displays: 162:Desktop simon$ sqlite3 test.db SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE test (id INTEGER, myText TEXT); sqlite> INSERT INTO test VALUES (1,''); sqlite> INSERT INTO test VALUES (2, NULL); sqlite> INSERT INTO test (id) VALUES (3); sqlite> SELECT * FROM test; 1| 2| 3| sqlite> SELECT id,myText,typeof(myText) FROM test; 1||text 2||null 3||null sqlite> .nullvalue NuLl sqlite> SELECT * FROM test; 1| 2|NuLl 3|NuLl sqlite> .quit > Most of what I am dealing with are text files with missing or non applicable > column details, as well as infrequent records with comments tacked onto the > end. > > To make it clear to myself whilst I'm messing with and confirming the data, > I've taken to setting the nullvalue to "¬" - a legacy of IBM keyboards which > is never used. This makes it clear to me that a field is definitely null as > opposed to a zero length string. You can convert your text file before you import the information into SQLite: replace all ,"" with ,NULL . Or you can do your import first, then use UPDATE commands to search for blank strings and change them to NULL. But inside any application which uses a SQLite database you should treat the two values as completely different: if you mean "Don't know" or "value missing" then store NULL, if you mean "I know what this is and it's blank" then store a zero-length string. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
