Use of sqlite3_create_collation on iPhone

Question

I would like to create a collation in order to make case insensitive and no accents searches in a database.

here is my code :

static int sqlite3NoCaseNoAccentCollate(void * foo, int ll, const void *l, int rl,
                         const void *r){
    NSLog(@"comparing");
    NSString *left = [NSString stringWithCharacters:l length:ll];
    NSString *right = [NSString stringWithCharacters:r length:rl];
    NSComparisonResult rs = [left compare:right options:NSCaseInsensitiveSearch|NSDiacriticInsensitiveSearch|NSForcedOrderingSearch];

    NSLog("%d",rs);
    return rs;
}


sqlite3 *db;
if (SQLITE_OK==sqlite3_open([pathToDataBase UTF8String], &db)){
    sqlite3_create_collation(db, "MYNOCASENOACCENT", SQLITE_UTF8, NULL, &sqlite3NoCaseNoAccentCollate);
}

When I execute this query :

SELECT ('teste' LIKE 'testé' COLLATE MYNOCASENOACCENT)

it returns 0 instead of 1, and my custom function is never called (I put breakpoints in the function to test).

When I list all the collations by using "PRAGMA collation_list", my custom collation exists.

Any idea of the problem ?

`stringWithCharacters:length:` expects a `unichar` (aka UTF-16) string. You probably have to use `initWithBytes:length:encoding:` and specify `NSUTF8StringEncoding`. - However, this does not probably not solve your problem if the comparison function is not called at all. — Martin R, Feb 04 '13 at 14:59
yes, for the "stringWithCharacters" i will use "stringWithUTF8String", it's simpler. like you said, it's not the real problem here :) — djleop, Feb 04 '13 at 15:01
Note that `stringWithUTF8String` can only be used if the strings are NULL-terminated. You may have to specify the length explicitly. — Martin R, Feb 04 '13 at 15:04
You could try `SQLITE_UTF16` instead of `SQLITE_UTF8` (and `stringWithCharacters:length:` in the comparison function). — Martin R, Feb 04 '13 at 15:20

score 0 · Accepted Answer · answered Sep 05 '13 at 10:46

It might be too late, hence just briefly:

You defined collation function. Will be used to compare table columns where that collation is specified. As you saw, it won't be applied for like operator.

What you need to do is to define and register a like function, for example

static void utf8likeFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    //...
}

sqlite3_create_function(db, "like", 2, SQLITE_UTF8, (void*)&likeInfoNorm, utf8likeFunc, 0, 0);

Sample like function can be found in sqlite sources.

I validate this answer because I believe in it, but I have not the time to test it. Thanks for this solution, i will study it later. — djleop, Sep 19 '13 at 15:36

Use of sqlite3_create_collation on iPhone

1 Answers1