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

Answer 1

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.