SQLite: "collate nocase"

2016-03-11 20:35  sqlitenocase    [permalink]

Learn something new each day! (Or also: I should have known about this!) SQLite is so strict! But in a good way. It's in the little things that come up now and then that you notice how delightfully strict it is designed, with reliability and safety up front. And no extra's. So it's quite logic that sorting a column with string values* is sorted case-sensitive, with al upper-case letters before the lower-case letters. To change this, add collate nocase either to the create table statement or the order by clause of queries. I've picked up somewhere that it's better for performance to have it in the create table statement, but I haven't checked this with the documentation or by testing in a test setup (yet), but for now I just went for updating the tables since changing all the queries would take much more work. That said, updating: not quite. Alter table doesn't have alter column apparently, but it does have a rename to, and insert into TblUpdate select * from Tbl works great, even with an autoincrement primary key (if you fields are still in the same order), so it's a relatively painless transition.

And this barely a week after I find out about the busy timeout!

(*: due to manifest typing different records may hold values of different type in the same field, but in practice you'll usually follow the type as determined by the schema.)

→