Clever Geek Handbook

SQLite order By places umlauts & speical chars at end

I am using Phonegap to create an iOS vocabulary app.

When querying the database for an alphabetical list, I use COLLATE NOCASE

:

ORDER BY term COLLATE NOCASE ASC

This solved the problem that terms starting with a lowercase letter were added to the end (chose from this question ).

However, non-standard characters like öduéê are still sorted at the end ~ here are 2 examples:

    Expected: Öffnungszeiten     Oberved: Zuzahlung
              Zuzahlung                   Öffnungszeiten

(or)          clé                         cliquer sur
              cliquer sur                 clé

I looked around and found similar issues discussed here here or here , but it seems that the general advice is to install any type of extension

This extension will probably help you ...

... use ICU as an extension

SQLite supports integration with ICU ...

But I'm not sure if this applies in my situation where the database is not self-hosted but is running on the client device. So I would suggest that I should ship this extension with my app bundle.
I am not very familiar with iOS, but I have a feeling that it will be difficult - at least.

Also in the official forum I found this hint:

SQLite does not properly handle accented characters.

and a little in text, the poster mentions a bug in SQLite.

All the links I found have been inactive for> = 1 year, and not all of them seem to deal with the mobile environment I'm currently developing in.
So I was wondering if anyone else would find a solution in their iOS projects.

The documentation states that there are only 3 default COLLATION options:

6.0 Sort sequences

When SQLite compares two strings, it uses the sort sequence or (two words for the same) to determine which row is greater or two are the same string. SQLite has three built-in grouping functions: BINARY, NOCASE, and RTRIM.

BINARY - Compares string data using memcmp(), regardless of text encoding.
NOCASE - The same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the
Comparison in progress

... Please note that only ASCII characters are randomly stacked. SQLite does not attempt to fully fold the UTF corpus due to the size of the tables required. RTRIM is the same as binary, except that trailing whitespace characters are ignored.

I currently prefer to do JavaScript sorting, but I suspect it won't do much good for overall performance.

+3


source to share


1 answer


The reason is that SQLite on iOS doesn't work with ICU enabled. So you need to create your own version of SQLite with ICU enabled + your own version of ICU as static lib + add ICU.dat and force SQLite to load that .dat file. Then you can load any collation with a simple SQL command (eg "icu_load_collation" ("de_DE", "DEUTSCH") ", once after opening the db).



Not only does it sound like mud work, but it actually is. Try to find the SQLite + ICU version and it's all done already.

+1


source






More articles:


All Articles