SQLite 3: character issue when ordering by records

Question

In my SQLite 3 database, I have some records with Turkish characters like "Ö", "Ü", "İ" etc. When I select my values ​​using the SELECT * FROM TABLE ORDER BY COLUMN_NAME query, records starting with these characters are at the end.

Typically, they should have been after the letter, which is a countless version of each of them. Like "..." after "O", "Ü" after "U".

Is this something about regional settings? Is there any way to control these settings?

I am using SQLite Manager in Firefox to manage my database.

Thanks in advance.

PS I know that this is not a solution for SQLite, but for those who need to use SQLite DB in Objective-C, they can sort the data array after retrieving from SQLite DB. Here's a good solution: How to sort an NSMutableArray with custom objects in it?

Answer 1

Unfortunately, there is no direct solution for this. At least for iOS. But there are ways to follow.

After I signed up for the SQLite mailing list, the Named user Jean-Christophe Deschamps came up with this answer:

"In my SQLite 3 database, I have records with Turkish characters like" Ö "," Ü "," İ ", etc. When I select my values ​​with" SELECT * FROM TABLE ORDER BY COLUMN_NAME ', the records starting with these characters go at the end. "

Lottery SQLite is only correctly mapped to lower ASCII encoding. Although this is good for plain English, it does not work for most of us.

"Usually they should be after a letter that does not have version points for each. Like" ... "after" O "," Ü "after" U ". Is this something about regional settings? Is there a way to control these settings?"

You have a choice between some ways to get this right or close to the right for your language (s):

o) use the ICU either as an extension (for third-party managers) or associated with your application. Advantages: it works 100% correctly for a specific language at a time in each operation. Disadvantages: they are huge and slow, and for this you need to register a collation for each language you deal with. Also, it will not work well for columns containing multiple non-English languages.

o) write your own settings (s) calling the ICU routines of your OS to compare strings. Advantages: Don't bloat your code with huge libraries. Disadvantages: it is required to write this extension (in C or something else), the same as other disadvantages as ICU.

o) If you use Windows, download and use the functions in the extension I wrote for close to the correct result. Advantages: it is small, fairly fast and ready to use, independent, but well suited for many languages ​​at the same time; it also offers several Unicode-aware string manipulation functions (sloppy or not), a fuzzy search function, and more. Comes as a source of C and x86 DLLs, free for any purpose. Disadvantage: it probably does not work 100% correctly for any language, using more than "vanilla English letters": I will sort your aimless, for example, by the dotted line i. This is a good compromise between absolute correctness for ONE language and "fair" correctness for most languages ​​(including some Asian languages ​​using diacritics) Download: http://dl.dropbox.com/u/26433628/unifuzz.zip

"I am using SQLite Manager in Firefox to manage my database."

My small extension will work with this. You might also want to try SQLite Expert, which has a built-in ICU (at least in its Pro version) and more.

Answer 2

These may be regional settings, but first I would check the UTF-8 encoding.