Custom SQLite collation not firing

Question

I'm trying to implement diacritic insensitive lookups in my SQLite database (on iOS). From what I've read, creating my own collation is the way to do this (please suggest alternatives if there are any). I've implemented the following:

static int _myFunction(void* context, int length1, const void* bytes1, int length2, const void* bytes2) {
    NSLog (@"Compare");
    ...
}

...

int collationResult = sqlite3_create_collation(db, "custom", SQLITE_UTF8, NULL, _myFunction);
NSLog (@"%d", collationResult == SQLITE_OK);

This returns without error. But when I run the following command, no results return and none of the code in my function is called. (I'm trying to get a result of "Côte")

SELECT * FROM team WHERE name LIKE \"Cote%\" COLLATE custom

Does anyone have any ideas?

score 2 · Accepted Answer · answered Jul 09 '14 at 06:38

2

As specified in the documentation, the built-in LIKE and GLOB operators always uses the NOCASE or BINARY collation.

You could override this by implementing your own user-defined like function.

answered Jul 09 '14 at 06:38

CL.

173,858
17
217
259

Thanks for pointing that rather important detail out to me. What are the general views of implementing a custom like function? Is it considered a bad practice? – Ricky Jul 09 '14 at 10:00
That depends on what alternate ways exist to solve the actual problem (whatever it might be). – CL. Jul 09 '14 at 12:02
I'm basically trying to get a select where statement to return results ignoring their diacritic value. (As in the example, "Cote" should return "Côte") – Ricky Jul 09 '14 at 12:47
To ask a question, use the "Ask Question" button. (But you should do a [search for your problem](http://stackoverflow.com/search?tab=votes&q=sqlite%20diacritics) first.) – CL. Jul 09 '14 at 12:52

Custom SQLite collation not firing

1 Answers1