2006-09Sep-25

Sorting

You find sorting algorithm in most programming languages, but you have to look hard for an implementation of common sorting algorithms in Visual FoxPro. Of course, Calvin Hsia has some on his blog, but other than that it's getting difficult. The most obvious reason is that Visual FoxPro supports sorting natively for indexes as well as arrays.

Sorting mixed strings

If you have data like

1/1A
1/1B
2/A
10/2
10/3

you want to sort the first part as if it's numerical. That is, the order should be 1, 2, 10, not 1, 10, 2 as it would happen, if Visual FoxPro treats this as a string. At the same time, however, you want to sort the remaining part in alphabetical order. The following index expression accomplishes that:

INDEX ON STR(VAL(field)) + field TAG _Sort

Sorting with COLLATE

Virtually every application picks only one COLLATION sequence it uses all the time. The majority uses MACHINE, followed by GENERAL, followed by country specific versions. However, sometimes you need to sort and search data with a different order. For example, you might use MACHINE as the collate sequence, but in some cases need to sort case-independent (that's just an example!).

When you create an index, Visual FoxPro uses the active COLLATE sequence or the one you specified with the COLLATE clause. A little known fact is that Visual FoxPro always uses the COLLATE sequence that belong to an index when Visual FoxPro searches or sorts using the index. This is true even when the application switched to a different COLLATE sequence:

Create Cursor curTest (cName C(10))
Insert into curTest Values("Straße")
Set Collate To "GERMAN"
Index on cName Tag cName
Set Collate To "MACHINE"
? Seek("Strasse")  && Prints .T.