FMDB: SQLite Statement ORDER BY orders diacritical errors incorrectly
I am very new to iOS development and I have a lot to learn. It's like a huge mountain, but thanks to all your help, I get places;)
I started an Xcode project (Xcode Version 6.1.1, Swift, iOS) and included FMDB to run SQLite queries. The queries run just fine, however in the following statement:
var resultSet: FMResultSet! = sharedInstance.database!.executeQuery("SELECT * FROM spesenValues ORDER BY country ASC", withArgumentsInArray: nil)
The alphabetical order is wrong from my point of view, but I don't understand how to fix it. The standard AZ characters are sorted where I expect them to be, but any character containing a diacritical character, eg. "..." is sorted by the very bottom of the list.
So I expect:
Österreich ... Zypern
But i get
Zypern ... Österreich
From Ordering SQLite To umlauts and speical chars at the end I found out it comes down to "SQLite on iOS doesn't come with ICU enabled".
Is there an easy way to set up FMDB to help me sort this "correctly". Thanks in advance and sorry if this turns into a crazy question
source to share
You can define your own SQLite function that it uses CFStringTransform
to remove 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)
}
}
Then you can use this new function unaccented
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 replacing "ä", "ö" and "ü" with "ae", "oe" and "ue" respectively. This is usually only done with names and place names (see Wikipedia entry for German spelling ), but if you want to do this, a custom function (which I renamed "sortstring"), replace these values appropriately:
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 only use this for sorting, you probably want to convert it to lowercase as well so that uppercase values are not separated from lowercase.
But the idea is the same, define whatever function you want to sort, and then you can use FMDB makeFunctionNamed
to make it available in SQLite.
source to share