On 12.05.2007 22:57 CE(S)T, Ingo Koch wrote: > Yves Goergen wrote: > >> I guess that doesn't work when I'm accessing the database through the >> System.Data.SQLite interface in .NET? > > Fortunately your guess is wrong. ;-) System.Data.SQLite supports > user defined collation sequences. See TestCases.cs of the source > distribution for samples how to implement them.
Thank you for the reply. I managed to try it out now and got it working really fast! Here's my solution, tightly adapted from TestCases.cs: /// <summary> /// User-defined collating sequence which does natural sorting. /// </summary> [SQLiteFunction(Name = "NATSORT", FuncType = FunctionType.Collation)] class NaturalSorting : SQLiteFunction { public override int Compare(string param1, string param2) { Match m1 = Regex.Match(param1, "^([0-9]+)"); if (m1.Success) { Match m2 = Regex.Match(param2, "^([0-9]+)"); if (m2.Success) { int cmpNum = int.Parse(m1.Groups[1].Value) - int.Parse(m2.Groups[1].Value); if (cmpNum != 0) return cmpNum; } } return String.Compare(param1, param2, true); } } Then, without anything else, do a query like: SELECT * FROM table1 ORDER BY column1 COLLATE NATSORT; You can even debug the user-defined function from VS 2005. I don't have the impression that it runs considerably slower with my 5000 records sorting on 3 solumns (where in many cases the first already decides). -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------