How do I order SQLite results containing umlauts and other special characters?
I would like to order my results from SQLite according to the rules used for German. This means that a character like "" is treated as "ae" or "" as "ue".
At this point, the solution looks like this:
SELECT * FROM data ORDER BY REPLACE(REPLACE(REPLACE(UPPER(einrichtung),' ','AE'),' ','OE'),' ','UE') LIMIT 0,20
The solution should not include installing additional tools or modifying the SQLite service, as this project should run out of the box wherever it is deployed.
source to share
I faced the same problem and it is still not possible.
This is a quote from the sqlite.org FAQ:
Q: Unicode case insensitivity does not work.
A: The default SQLite configuration only supports case insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive and case-insensitive cases requiring tables and logic has nearly doubled the size of the SQLite library. The SQLite developers believe that any application that needs full Unicode support may already have the required tables and functions, and therefore SQLite should not take up space to duplicate this capability. Rather than supporting full Unicode support by default, SQLite provides the ability to bind to external Unicode mapping and transformations. The application can overload inline NOCASE (using sqlite3_create_collation ()) and inline like (), upper () and lower () (using sqlite3_create_function ()).The SQLite source code includes "ICU", an extension that makes these overloads. Or developers can write their own overloads based on their own Unicode-compliant comparison routines already contained in their project.
source to share
You have to do everything in UTF-8 / UTF-16. If you are dealing with PHP, for example, you are also working with utf8_encode / utf8_decode.
See also http://www.sqlite.org/pragma.html#pragma_encoding
(I know the question is old, but people still have problems with the correct encoding.)
source to share
Hey, this is the third million, man, no special characters anymore - just characters.
However, it looks like you want to change the sorting behavior among these characters. This extension will probably help you. If you don't like any of the mappings provided, I think you can program them yourself as described here
source to share