German Umlaute and SQLite3?

Question

Level 1

24 points

German Umlaute and SQLite3?

I'm struggling with SQLite 3 and German Umlaute (ä, ö, ü, etc.)

It seems as if I cannot do WHERE x LIKE y clauses with German Umlaute recognized. Here's a code excerpt:


const char *sql = "SELECT zip, city, number, mobile, active, remarks FROM rawData WHERE city LIKE ?";
if (sqlite3preparev2(database, sql, -1, &searchCityStatement, NULL) != SQLITE_OK) {
 NSAssert1(0, @"Error: Failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
}
sqlite3bindtext(searchCityStatement, 1, [c UTF8String], [c length], SQLITE_TRANSIENT);

So the idea here is that I search for rows WHERE city LIKE <parameter c>. It works fine without Umlaute. E.g. rows like Berlin, London, etc. are returned. However, when I search e.g. for Lünen, it does not work. I believe that the [c UTF8String] strips out Umlaute, but just using c does not work, either.

Any help?

iMac, Mac OS X (10.5.4), iPhone 2.0.3

Posted on Sep 9, 2008 3:29 AM

Reply

Answer 1

fuggly

Level 1

4 points

Jan 19, 2009 11:01 AM in response to BeSharp

I can see that you try to bind with UTF-8 where no UTF-8 exists (char *), so have you ever tried using an NSString for the ql statement:

NSString *sql = @"SELECT....."

?

Reply

Answer 2

Jan 20, 2009 4:22 AM in response to BeSharp

SQLite does not properly handle accented characters. From the SQLite documentation:

The LIKE operator is not case sensitive and will match upper case characters on one side against lower case characters on the other. (A bug: SQLite only understands upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.).

Although it does not explicitly state it, 'Lünen' LIKE 'Lunen' is FALSE.

Reply

Answer 3

May 4, 2009 1:15 AM in response to Laurie Davis

Any ideas for a workaround?
I also have columns that contain data with german umlauts and I want to be able to search for the using the WHERE x LIKE '%Döner%' or something like that.

Reply