Back to Blog
Sqlite order by ignorecase7/14/2023 ICU needs to be compiled before use, potentially for different OS and platforms (not tested). It is a new type of dependency: not a Python library, but an extension that should be distributed together with the application. It may even be faster than some of the later solutions since it is written in C and is more tightly integrated with SQLite. ICU solves the problems of both case-insensitive LIKE and comparison/search, plus adds support for different collations for a good measure. ICU stands for International Components for Unicode. Official SQLite documentation mentions the ICU extension as a way to add complete support for Unicode in SQLite. We will look at the pros and cons of each approach, implementation details, and, finally, at indexes and performance considerations. Some of these solutions can be adapted to other databases and for implementing Unicode-aware LIKE, REGEXP, MATCH, and other functions, although these topics are out of the scope of this post. I needed the full Unicode spectrum, so a better solution was in order.īelow I summarize five ways to achieve case insensitive search/comparison in SQLite for all Unicode symbols. It is not a problem if you plan to work with strings that contain only English alphabet letters, numbers, etc. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. SQLite only understands upper/lower case for ASCII characters by default. Second, and more importantly, they have a rather limited understanding of what case-insensitive mean: More on the use of indexes for case-insensitive queries is below. First, without special considerations they do not make use of indexes on the field they are working on, with LIKE being the worst offender: in most cases it is incapable of using indexes. Enter fullscreen mode Exit fullscreen modeĪll these approaches are not ideal.
0 Comments
Read More
Leave a Reply. |