Can I index first n char of a column in sqlite?

Question

Official docs don't throw any light on this. I want to index first n char on my column to save memory space. Is it even possible with sqlite? If so, How can I achieve that?

Answer 1

You can put an index on an expression:

CREATE INDEX xxx ON MyTable(substr(MyColumn, 1, 10));

This index will be used only when a query expression matches the index expression, e.g.:

SELECT * FROM MyTable WHERE substr(MyColumn, 1, 10) = ?;

---

This feature requires SQLite 3.9 or later, which is available only in Android 7 ("Nougat") or later.