case insensitive order by in SQLite in .net

Question

I'm using SQLite from a C# program using SQLite.net ( http://sqlite.phxsoftware.com ).

By default SQLite select order by clause sort is case sensitive, I want the result to sort case insensitive, I found "COLLATE NOCASE" but the documentation says it will only handle English characters in the ascii range, I want true linguistic international case insensitive sort using the CultureInfo.CurrentCulture collation (making it use String.Compare will do the trick).

Answer 1

I believe such collation is not provided in current versions of SQLite. As such it would seem that the most sensible plan is to remove the sort from the query and instead sort afterwards in pure .Net where you have full control and access to constructs like the thread's culture info.

Since both are happening in the same process this shouldn't make a big difference in performance terms unless your dataset is very large.

SQLite 3 does allow user defined collation functions, and these can be done in SQLite.Net as .net functions but the overhead of calling back and forth across the managed/unmanaged boundary is considerable. Here is one persons attempts to do it in c++. Unless you have access to someone else's well tested and stable unicode culture sensitive sort in C++ I suggest sticking to the simple sort after approach where possible.

Of course if the performance of the user defined collation is more than enough for your present needs then go with that.

[SQLiteFunction(Name = "CULTURESORT", FuncType = FunctionType.Collation)]
class CultureSort : SQLiteFunction
{
    public override int Compare(string param1, string param2)
    {
        return String.Compare(
            param1,param2, CultureInfo.CurrentCulture, CompareOptions.IgnoreCase)
        );
    }
}

Post script if you fancy getting fancy: There is a blessed build of SQLite which integrates the ICU library for 'proper' unicode support on ordering/like/upper/lower but you would need to integrate that into the sqlite code used as the backing for the .Net wrapper. This many not be easy.