Understanding Sort Orders

Sort orders incorporate the sorting rules of different locales, making it possible for you to sort data in those languages correctly. In Visual FoxPro, the current sort order determines the results of character expression comparisons and the order in which records appear in indexed or sorted tables.

Note

Sorting works differently in double-byte character sets (DBCS) environments.

Use the appropriate sort order, because different sort orders produce different results, as shown in the following table.

Unsorted	Machine	General	Spanish
!@#$	Space	space	Space
1234	!@#$	!@#$	!@#$
space	1234	1234	1234
Caesar	Caesar	a	a
csar	Car	ab	Ab
Strasse	Char	b	b
strae	Czech	Caesar	Caesar
Car	Strasse	csar	Csar
Char	Ab	Car	Car
Czech	Csar	ar	ar
ab	Strae	Char	Czech
ar	ar	Czech	Char
a	a	Strasse	Strasse
b	b	strae	Strae

Sort Order Guidelines

Consider the following guidelines when choosing a sort order:

• Avoid the Machine sort order if you want to sort international characters properly, because Machine sorts international characters in ASCII order. For example, notice that ar follows strae.

• Characters with diacritical marks sort differently than characters without diacritical marks. For example, in the General and Spanish sort orders, notice that a sorts before ab but ab sorts before b.

• Ligatures such as sort the same as their equivalent character expansions. For example, strae sorts the same as Strasse, and csar sorts the same as Caesar.

• In some languages, two characters sort as a single character. For example, in Spanish the Ch in Char sorts as a character between C and D.

The following sections describe how to specify sort orders, check the current sort order, and recognize the effects of sort orders.

Checking Sort Orders

You can determine the current sort order by using the SET ('COLLATE') function. For example, you can save the current sort order, set the current sort order to Machine, perform whatever work is necessary, and then restore the original sort order by using the following code:

cCurrentOrder=SET('COLLATE')
SET COLLATE TO 'MACHINE'
* code that requires the Machine sort order
SET COLLATE TO cCurrentOrder  && return to the previous sort order

You can also determine the sort order of an index or index tag by using the IDXCOLLATE( ) function.

Recognizing the Effects of Sort Orders

The sort order affects the results of string comparisons, SEEK, and SELECT - SQL, as described in the following sections.

Comparing Strings

All sort orders except for Machine and Unique Weight ignore case. This means that you don't have to use UPPER( ) in your index expressions.

The current sort order affects string comparisons. For example, when you set the sort order to General, the following statements return True (.T.):

?"A" = "a"
?"Strae"="Strasse"
?"" = "ae"

However, when you use the Machine sort order, all of these statements return False (.F.). because the strings are matched for an exact comparison, byte by byte.

The character string comparison operator (= =) gives you the same result as when you compare by value or when you compare using the Machine sort order; that is, it compares strings byte by byte. For example, the following statement returns False (.F.):

? "Strae" == "Strasse"

Note

Visual FoxPro ignores SET EXACT when you use the character string comparison operator (= =).

Using SEEK

Visual FoxPro ignores diacritical marks when you perform a partial seek. A partial seek occurs when you make the length of the expression less than the length of the key. If diacritics are important, consider using the SCAN ... ENDSCAN or LOCATE commands instead of SEEK.

The advantages of using SCAN and LOCATE instead of SEEK include the following:

• SCAN and LOCATE are sensitive to diacritics.

• Visual FoxPro fully optimizes the results of SCAN or LOCATE if the current sort order is Machine or Unique Weight, whereas Visual FoxPro only partly optimizes the results of SEEK.

• SCAN and LOCATE remember the condition that invoked them, making it possible for you to use them for looping on a condition. In contrast, SEEK positions you somewhere in the index, and SKIP continues down the index from that point. Accordingly, SEEK might not produce the results you want with international data.

Using SELECT - SQL

The SELECT - SQL command uses the current sort order. For example, if you have an index tag based on the General sort order and the current sort order (returned by SET ('COLLATE')) is Machine, the result of SELECT SQL is based on Machine.

To employ the current sort order, use the ORDER BY clause of SELECT - SQL.

Using Indexes

Sort orders determine the order of records in indexed tables. Consider the following guidelines for using indexes with sort orders:

• Rebuild indexes created in earlier versions of FoxPro if you want the indexes to use a sort order other than Machine.

• Rebuild dBASE indexes to take advantage of Visual FoxPro sort orders.

• Use the REINDEX command to rebuild an index, because REINDEX leaves the sort order unchanged.

See Also

Concepts

Sorting Data in International Applications

Application Creation with Double-Byte Character Sets

Reference

Code Pages Supported by Visual FoxPro

Data Tab, Options Dialog Box

Modifying International Applications

Other Resources

Developing International Applications