FMDB: SQLite Statement ORDER BY orders diacritics incorrectly

Question

I am very new to iOS development and there is a lot for me to learn. It is like a huge mountain, but thanks to all your help I am getting places ;)

I have started an Xcode Project (Xcode Version 6.1.1, Swift, iOS) and included FMDB to run SQLite queries. The queries get executed just fine, however in the following statement:

var resultSet: FMResultSet! = sharedInstance.database!.executeQuery("SELECT * FROM spesenValues ORDER BY country ASC", withArgumentsInArray: nil)

The alphabetic order is wrong from my point of view, but I don't seem to figure out how to fix it. The standard A-Z characters get sorted, to where I would expect them, but any character containing a diacritic symbol, e.g. ÄÖÜ is sorted to the very bottom of the list.

So what I expect is:

Österreich ... Zypern

But what I get is

Zypern ... Österreich

From SQLite Order By places umlauts & speical chars at end I learned that it is down to the fact that "SQLite on iOS doesn't come with ICU enabled".

Is there an easy way to configure FMDB to help me sort this "correctly". Thank you in advance and sorry if this turns into a super dumb question

Rob · Accepted Answer · 2017-05-26T15:07:04.673

You can define your own SQLite function that uses CFStringTransform to remove the accents. Using FMDB 2.7:

db.makeFunctionNamed("unaccented", arguments: 1) { context, argc, argv in
    guard db.valueType(argv[0]) == .text || db.valueType(argv[0]) == .null else {
        db.resultError("Expected string parameter", context: context)
        return
    }

    if let string = db.valueString(argv[0])?.folding(options: .diacriticInsensitive, locale: nil) {
        db.resultString(string, context: context)
    } else {
        db.resultNull(context: context)
    }
}

You can then use this new unaccented function in your SQL:

do {
    try db.executeQuery("SELECT * FROM spesenValues ORDER BY unaccented(country) ASC" values: nil) 

    while rs.next() {
        // do what you want with results
    }

    rs.close()
} else {
    NSLog("executeQuery error: %@", db.lastErrorMessage())
}

You suggest that you want to replace "ä", "ö", and "ü" with "ae", "oe", and "ue", respectively. This is generally only done with proper names and geographical names (see Wikipedia's entry for German orthography), but if you wanted to do that, have your custom function (which I've renamed "sortstring") replace these values as appropriate:

db.makeFunctionNamed("sortstring", arguments: 1) { context, argc, argv in
    guard argc == 1 && (db.valueType(argv[0]) == .text || db.valueType(argv[0]) == .null) else {
        db.resultError("Expected string parameter", context: context)
        return
    }

    let replacements = ["ä": "ae", "ö": "oe", "ü": "ue", "ß": "ss"]

    var string = db.valueString(argv[0])!.lowercased()

    for (searchString, replacement) in replacements {
        string = string.replacingOccurrences(of: searchString, with: replacement)
    }

    db.resultString(string.folding(options: .diacriticInsensitive, locale: nil), context: context)
}

By the way, since you're using this just for sorting, you probably want to convert this to lowercase, too, so that the upper case values are not separated from the lower case values.

But the idea is the same, define whatever function you want for sorting, and then you can use FMDB's makeFunctionNamed to make it available in SQLite.

Thank you for that suggestion, it sounds interesting. But removing accents and diacritics might not be the best choice for me, altough it is an appealing approach :) According to "German sort logic" the character Ö actually represents Oe and that is where it needs to be sorted to. As my SQLite Database is fairly limited, I might just cheat by adding a new column with the spelled transcribed names like Österreich to Oesterreich and then search in the former but sort by the latter... Nasty hack, I am not even happy to put this down in writing, as it is that embarrassing. Thank you for your help! — Claus Wolf, Dec 20 '14 at 15:21
If you want to replace the umlaut vowels (and presumably the "ß") with two ASCII characters, just do that in your function, too. (I must confess, though, that sorting "östlich" between "od" and "of" doesn't feel right, is certainly not what dictionaries do, but I know geographic names have special rules, so do whatever you want.) See revised answer. The question of denormalizing the database, as you suggested in your comment, or calling a function in the SQL, is a question of performance optimization, and is a completely different question. — Rob, Dec 20 '14 at 19:58
this is awesome and you actually reminded me of an important aspect of sorting German - one never stops learning. I was so focused on names and place names that I ignored that most other terms would treat ä as a - but when it comes to names ä is treated as ae to ensure that Mueller and Müller will be in the same place. Thank you so much - I cannot thank you enough for the time and effort you have taken here. — Claus Wolf, Dec 20 '14 at 22:07
@AmitJageshaシ - We've updated FMDB to 2.7, which exposes methods to more easily create these custom functions in Swift 3. (FMDB 2.7 also was audited for nullability, so the optionality of many parameters has changed, so there will be some code changes when you convert.) See revised answer above. — Rob, May 26 '17 at 15:10

FMDB: SQLite Statement ORDER BY orders diacritics incorrectly

1 Answers1