How to 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 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.

---

I just ran into the same problem and it is still not possible.

This is a quote from the FAQ of sqlite.org:

Q: Case-insensitive matching of Unicode characters does not work.

A: The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive comparisons and case conversions requires tables and logic that would nearly double the size of the SQLite library. The SQLite developers reason that any application that needs full Unicode case support probably already has the necessary tables and functions and so SQLite should not take up space to duplicate this ability. Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines. The application can overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in like(), upper(), and lower() functions (using sqlite3_create_function()). The SQLite source code includes an "ICU" extension that does these overloads. Or, developers can write their own overloads based on their own Unicode-aware comparison routines already contained within their project.

---

You have to do everything in UTF-8/UTF-16. If you are dealing with PHP for example you have also work with utf8_encode/utf8_decode.

See also http://www.sqlite.org/pragma.html#pragma_encoding

(I know the question is old, but people still having problems with proper encoding.)

---

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