CODEWITHSUNDEEP
iosobjective-csqlite
Chris Schiffhauer
Chris Schiffhauer

Reputation: 17290

Unable to perform accent/diacritics-insensitive search in SQLite with Objective C

I am trying to put together a solution based on various examples I've found on SO and elsewhere.

I have a SQLite database with a column that has accented strings, i.e. église. I want a search for eglise to match église.

I have a user-defined function outside my implementation:

void withoutDiacritics(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    if (argc != 1 || sqlite3_value_type(argv[0]) != SQLITE_TEXT) {
        sqlite3_result_null(context);
        return;
    }

    @autoreleasepool {
        NSMutableString *string = [NSMutableString stringWithUTF8String:(const char *)sqlite3_value_text(argv[0])];
        CFStringTransform((CFMutableStringRef)string, NULL, kCFStringTransformStripCombiningMarks, NO);
        sqlite3_result_text(context, [string UTF8String], -1, SQLITE_TRANSIENT);
    }
}

Prior to querying, I create the function:

sqlite3_create_function_v2(db, "WithoutDiacritics", 1, SQLITE_ANY, NULL, &withoutDiacritics, NULL, NULL, NULL);

And finally, I query against an FTS3 virtual table:

SELECT * FROM vTerms WHERE WithoutDiacritics(MasterField) MATCH '*eglise*'

Unfortunately, nothing is returned, even if eglise exists without accents in the table.

How do I apply my function to the database column?

P.S. I have explored the backup possibility of including a column without diacritics for fast searches. It works well but the larger database size puts me over the iOS App Store's 100mb download limit.

Upvotes: 1

Views: 591

Answers (1)

CL.
CL.

Reputation: 180080

FTS tables do not use collations; you need to use a different tokenizer like ICU or UNICODE61, or write a custom tokenizer.

Upvotes: 1

Related Questions