Error: no such collation sequence: NOCASE_UTF8

Question

I m using an existing db file in sqlite3. One of the column in a particular table is having clause COLLATE NOCASE_UTF8. I m not able to insert any text in that field. its giving following error:

sqlite> INSERT INTO recording (recordingName) VALUES ('abcd');
Error: no such collation sequence: NOCASE_UTF8

How should I enter the text in it?

Answer 1

That's a custom collation. SQLite has native support for a collation named "NOCASE", but not for a collation named "NOCASE_UTF8". You're getting the error, I think, because there is no such collation defined in your SQLite database, but nevertheless that collation name is still stored as part of the table definition.

I'm not sure how SQLite handles storage of collations defined through the C API. But I'm pretty sure that's what you need to fix. See also the docs for Collation Needed Callbacks. (You might need only one call to sqlite3_collation_needed().)