Simon, Didn't know about the "typeoff" function. Is this anything like the CAST sql syntax or is it peculiar to sqlite?
At the moment I am not too worried about referrential integretity - just trying to get many plain text files of numerous formats into a siingle database from which I can attempt to merge them. Missing fields are a major concern in this respect. There might not even be a primary key on the raw tables at this stage. By the way this isn't, at least not yet, going to be linked into fortran, ADA, or a python application. The intention is to simply meld some dubious text file formats from differing countries into some sort of a unified database. Thanking you for the clarification regarding the treatment of empty strings in sqlite, Matthew --- On Sat, 13/8/11, Simon Slavin <slav...@bigfraud.org> wrote: From: Simon Slavin <slav...@bigfraud.org> Subject: Re: [sqlite] null handling import To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Date: Saturday, 13 August, 2011, 17:20 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
