Alright, so I made the modifications as so to the Categories table: CREATE TABLE [Categories] ( [CatID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] CHAR COLLATE NOCASE);
CREATE UNIQUE INDEX [idxCatName] ON [Categories] ([Name] COLLATE NOCASE); Does this make more sense? I have a row with Name field stored as CPU. Pre-modification to the table, I ran the SQL command [ select * from Categories where Name='cpu' ] and received no results. Modified the table and ran the same command, and I got a result. So both index and table is created with NOCASE. On Sat, Sep 14, 2013 at 12:25 PM, Keith Medcalf <[email protected]> wrote: > > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
