Or to get all the non-integer records.
select * from qqq where typeof(field1) <> 'integer'; Mike -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert Sent: Saturday, June 08, 2013 9:58 AM To: rsm...@rsweb.co.za; General Discussion of SQLite Database Subject: Re: [sqlite] Strange table behaviour after text import with sqlite3.exe Aaah, OK, that answers my question! This is something I hadn't realised at all and good to know that one. Thanks for clearing this up. RBS On Sat, Jun 8, 2013 at 3:48 PM, RSmith <rsm...@rsweb.co.za> wrote: > Yes, FIELD1 values are formatted int he output to be displayed as 0 since > it is a INTEGER field, but the real value of FIELD1 is "FIELD1" for the 0th > record, since that is what was imported from the CSV. The formatted value > is not always the same as the real value. > > try: > > select * from qqq where field1 = "FIELD1" > > > It will pop out a record I'm sure. > > > > On 2013/06/08 16:42, Bart Smissaert wrote: > >> Have table defined like this: >> >> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT) >> >> Table is empty, so has no records. >> >> Then I import a text file with this data: >> >> FIELD1,FIELD2 >> 1,ABC >> 2,BCD >> 3,CDE >> >> This is via sqlite3.exe with: >> >> .mode csv >> .import textfilename QQQ >> >> Table will then be like this: >> >> FIELD1 FIELD2 >> ------------------------------**--- >> 0 FIELD2 >> 1 ABC >> 2 BCD >> 3 CDE >> >> This is all fine and as expected. >> However I am unable to produce any records when doing a select >> with a where clause specifying field1 to be zero. >> >> Tried all: >> select * from qqq where field1 = 0 >> select * from qqq where field1 = '0' >> select * from qqq where field1 = '' >> select * from qqq where field1 is null >> >> Nil producing a record. >> >> Any idea what is going on here or what I might be doing wrong? >> >> >> RBS >> ______________________________**_________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqli te.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<http://sqli te.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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
