> As for the case, I wanted to make absolutely certain that when I update > based on text and not an ID, the most recent form of the text is put > into play.
> I didn't want to have the software add Motherboard and > MotherBoard as two distinct rows, so I went all out on making sure that > everything > SQLite deals with considers case to be not considered. Declaring the table column with COLLATE NOCASE is "case preserved but ignored" so the value stored will be in the actual case given (ie 'MotherBoard') but for the purposes of indexes, comparisons, etc., case will be ignored, unless you specify otherwise by specifying collate binary. And you do not have to convert the case of data anywhere -- basically it is treated as always folded to a single case in any operation referring to the column. If you really want to ensure that the "latest" version of the case preserved text is used, you can always do something like: insert or ignore into data (value) value (:value); update data set value = :value where value = :value and value collate binary != :value; select id from data where value = :value; which will insert the :value if it does not exist, returning the id. The update will update the value with the provided :value where the "case insensitive comparison" is true (value = :value) and the "case sensitive comparison" is false (value collate binary != :value). Though it might be easier to update all the cased values afterwards to make them pretty if you want: Update data set value = 'MotherBoard' where value = 'MotherBoard"; Will reset the stored case-preserved value based on a case insensitive comparison. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
