How to order SQLite-results containing umlauts and other special characters?

Question

I would like to order my results from SQLite according to the rules used for German. This means a character like "�" is treated like "ae", or "�" like "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 involve installing more tools or modifying the SQLite service, because this project should run "out of the box" wherever it will be deployed.

Answer 1

Hey, it's the third millenium, dude, there are no special characters anymore - just characters.

That said, it seems you want to change the collation behaviour among those characters. This extension can probably help you. If you don't like any of the provided collations, I think you can program one yourself, as described here