Character that comes last in sorting texts-strings

krikikriki Member, Moderator Posts: 9,094
edited 2007-01-29 in NAV Tips & Tricks
The character that comes last in order in general is not 'Z' or 'z' or '9' but some 'exotic' character.

This trick is interesting in case you want to convert a
SETFILTER("Some Field",'%1','abc*');
in a
SETRANGE("Some Field",'abc','abc' + PADSTR('',5,MaxSortingChar()));

and probably gaining performance.


And this is the code for the function MaxSortingChar
MaxSortingChar() : Text[1]
// MaxSortingChar
// gives the char that comes last when sorting

LtexLast := '';
FOR Lint := 0 TO 255 DO BEGIN
  Lcha := Lint;
  LtexTest := FORMAT(Lcha);
  IF LtexLast < LtexTest THEN
    LtexLast := LtexTest;
END;
EXIT(LtexLast);
Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Comments

  • kinekine Member Posts: 12,562
    Have you tried to measure the gain of performance?

    =D> =D>
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • ara3nara3n Member Posts: 9,255
    Interesting. The Iint was 206 when I ran it my sql db.

    Ran it on client sql I got 190
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • David_SingletonDavid_Singleton Member Posts: 5,479
    Do you have the same collation?
    David Singleton
  • ara3nara3n Member Posts: 9,255
    No they are same Latine1_General

    but one is case sensitive the the other isn't



    Latin1_General_CS_AS
    Latin1_General_CI_AI

    that explains it.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • krikikriki Member, Moderator Posts: 9,094
    kine wrote:
    Have you tried to measure the gain of performance?

    =D> =D>
    No, but I know that a SETRANGE can be better than a SETFILTER on a Navision DB. I have also had that the SETFILTER performs as fast as a SETRANGE even when I didn't expect it and with a cold DB.
    But still : use SETRANGE if possibile and not SETFILTER.

    ara3n This is the reason I created this routine. I had noticed the sorting-problem between Navision and SQL and with this trick I invented I could avoid possible problems
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.