case insensetive 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.Compere will do the trick).

Thanks

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.

Answer 2

Aye, this is much more complex that you would guess it is. In some cultures the rules are different and without know what culture you want to base it off of it becomes impossible. Consider the famous (amoung i18n people) example of Turkish.

Answer 3

SQLite.NET allows you to define custom functions in C# that you can use in SQL queries. So it should be possible to write a custom_upper or custom_lower function in C# that would handle the case conversion with the current culture collation...