Character that comes last in sorting texts-strings

kriki · 2007-01-25

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);

kine · 2007-01-26

Have you tried to measure the gain of performance?

=D> =D>

ara3n · 2007-01-26

Interesting. The Iint was 206 when I ran it my sql db.

Ran it on client sql I got 190

David_Singleton · 2007-01-26

Do you have the same collation?

ara3n · 2007-01-26

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.

kriki · 2007-01-29

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

Character that comes last in sorting texts-strings

Comments

Categories