Dear Phil The comma/colon delimited records are from a relatively simple parse through an awk script.
I've modified all scripts which replaces empty strings with 'null' , and every things hunky dory. I was just wondering if sq3 has some kind of mechanism to parse blank records as null instead of empty strings. It is easily fixed, and not a problem, but just complicates matters when you try to check consistency twice over (in awk and in SQL - typogiphacal errors have been found in both, so a kind of a good thing). I sort of understand the manifest typing, and three valued logic, but was trying to clarify whether or not dot_importing supports NULLs at all? Sincerely, Matthew n Sat, 13/8/11, H. Phil Duby <phild...@phriendly.net> wrote: From: H. Phil Duby <phild...@phriendly.net> Subject: Re: [sqlite] null handling import To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Date: Saturday, 13 August, 2011, 4:20 On Fri, Aug 12, 2011 at 14:59, matthew (matthew.jsoft) white <matthew.js...@btinternet.com> wrote: > > This may seem obvious for seasoned users, but sqlite3 always turns every > blank input token into an empty string. > > For example, say you are dot.importing a colon separated file such as :- > > create table foo ( col1 integer, col2 varchar(100) ); > > .separator ":" > > 1001:'a_string' -- dynamic string into column 2 > 1002:'' -- string of length two " into column 2 > 1003:null -- explicit null into column 2 > 1004:: -- empty type-less value into column 2 > > Would there be any way of reading an external data file into a table with > blank fields being replaced by nulls? Assuming you do not have things like :: inside of strings, you could pre-process the text file, replacing :: with :null:, ^: with ^null:, and :$ with :null$, ^ and $ being the start of line and end of line markers respectively. If :: can exist inside of strings, the substitution gets trickier to do right. [ .. snip .. snip .. ] _______________________________________________ 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
