Re: [sqlite] Re: How to sort not binary?
On 22.06.2007 17:48 CE(S)T, Yves Goergen wrote:
> 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);
Oh, well, I just realised that this will only sort numbers "naturally"
at the beginning of strings, but not in the middle or at the end. It
will be a bit more complex to do that. Maybe I find a sort/compare
algorithm for it. But at least I know now that it's easy to use. :)
--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: How to sort not binary?
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:
///
/// User-defined collating sequence which does natural sorting.
///
[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]
-
Re: [sqlite] Re: How to sort not binary?
Yves Goergen schrieb: > On 13.05.2007 17:19 CE(S)T, Chris Wedgwood wrote: >> On Sun, May 13, 2007 at 05:07:16PM +0200, Yves Goergen wrote: >> >>> Ah, now I realised that I'd also like to have that "natural sorting", >>> meaning this: >>> >>> 2 >>> 8 >>> 9 >>> 10 >>> 11 >>> 23 >> select from from table order by cast( as text); > > I'm not sure what you wanted to say with this. I removed the first > "from" to make it work but it does the same as without the cast. It > still sorts strings beginning with "10" before those beginning with "2". Yep, because the statement is wrong for your case. I guess he misunderstood you somehow. select from order by cast ( as integer) is what you wanted. But: If your column is a text column holding strings and numbers or strings beginning with numbers the statement above won't work as expected. All rows starting with alpha text will be placed at the beginning of the result. They all return 0 as the integer value and are not sorted but returned in the order they have been added to the table. If your column to sort on only holds integer values and you defined the column as INTEGER your "natural sorting" should be handled by SQLite. If you have strings *and* numbers in your columns then write a user defined collation sequence which handles this case. It's up to you. ;-) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
On 5/13/07, Yves Goergen <[EMAIL PROTECTED]> wrote: On 12.05.2007 22:57 CE(S)T, Ingo Koch wrote: > 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. Ah, now I realised that I'd also like to have that "natural sorting", meaning this: ... Can this be done with a user-defined collation, too? How does this all impact on the performance? I have over 5.000 rows to sort on 3 columns and would like to do that in near-real-time. It's up to you to feed with a collation that does natural sort. A few thousand rows are not much in modern computer terms, but I have no idea on the impact in terms of performance the .NET wrapper has. Test it and you'll know. Regards, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
On 13.05.2007 17:19 CE(S)T, Chris Wedgwood wrote: > On Sun, May 13, 2007 at 05:07:16PM +0200, Yves Goergen wrote: > >> Ah, now I realised that I'd also like to have that "natural sorting", >> meaning this: >> >> 2 >> 8 >> 9 >> 10 >> 11 >> 23 > > select from from table order by cast( as text); I'm not sure what you wanted to say with this. I removed the first "from" to make it work but it does the same as without the cast. It still sorts strings beginning with "10" before those beginning with "2". -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
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. Ah, now I realised that I'd also like to have that "natural sorting", meaning this: 2 8 9 10 11 23 instead of: 10 11 2 23 8 9 Can this be done with a user-defined collation, too? How does this all impact on the performance? I have over 5.000 rows to sort on 3 columns and would like to do that in near-real-time. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
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. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
On 5/12/07, Yves Goergen <[EMAIL PROTECTED]> wrote: On 12.05.2007 17:33 CE(S)T, Igor Tandetnik wrote: > Yves Goergen <[EMAIL PROTECTED]> > wrote: >> I'm trying to get my table sorted the way how for example Windows >> Explorer or other file managers are sorting names. Most of all, >> accented characters should not be listed at the end of the list but >> near their non-accented character instead. I can only see the >> built-in collations BINARY and NOCASE. Is there something else? > > You need to install a custom collation. See sqlite3_create_collation[16] I guess that doesn't work when I'm accessing the database through the System.Data.SQLite interface in .NET? I did the same question about ordering, in my case, with Perl DBD::SQLite. As the answer was the same (and I understand why), i did the.. erm... stupid approach: . a column with the word/string or whatever . a column with the string where accented characters are not (accented). Use the first for presentation and query, the second for ordering. Hope this can help Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
On 12.05.2007 17:33 CE(S)T, Igor Tandetnik wrote: > Yves Goergen <[EMAIL PROTECTED]> > wrote: >> I'm trying to get my table sorted the way how for example Windows >> Explorer or other file managers are sorting names. Most of all, >> accented characters should not be listed at the end of the list but >> near their non-accented character instead. I can only see the >> built-in collations BINARY and NOCASE. Is there something else? > > You need to install a custom collation. See sqlite3_create_collation[16] I guess that doesn't work when I'm accessing the database through the System.Data.SQLite interface in .NET? -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
