2

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.

fboes
  • 321
  • 1
  • 5

3 Answers3

1

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.)

Tick Tac Joe
  • 43
  • 7
0

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.

favo
  • 5,426
  • 9
  • 42
  • 61
-4

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

Kilian Foth
  • 13,904
  • 5
  • 39
  • 57
  • Yeah, I wish they were no special characters anymore. But just look at the umlauts in my posting. :) Thx for your answer, but I was hoping there would be a solution without recompiling SQLite or installing any other tools. But the more I search for such a solution, the more I think there is none. :( – fboes Jul 26 '10 at 07:25