Sqlite LIKE optimization is described as follows: A like pattern of the form "x LIKE 'abc%'" is changed into constraints "x>='abc' AND x<'abd' AND x LIKE 'abc%'"
If you look into sqlite code, then the string "abd" is generated from "abc" using a trivial algebra. However, this algebra won't work on a custom collation. Right now I am investigating a similar issue (we use custom case-insensitive collations) and come to the conclusion that the LIKE optimization cannot be done without the specific knowledge of the collation used. In other words, it cannot be done by the Sqlite engine. Two extreme examples: CZE/SVK: If you replace "x LIKE 'ch%'" by "ch < x AND x < 'ci'", you'll get no results. DAN/NOR: How would you replace "aa%"? (Note this correct sorting: Zorro < Aaron) Or what about this: "\u0061\u030A"? (Å written with combining diacritics.) Look here <http://en.wikipedia.org/wiki/Alphabetical_order> for more problems. -- View this message in context: http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp73789p75617.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
