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]
-----------------------------------------------------------------------------
