Sorting Data in International Applications

Article
10/23/2006

After creating a table of international data, check to see if your application sorts the data correctly. How the data sorts depends on the code page associated with the table, because the code page specifies the available sort orders or collation sequences.

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 (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.

Specifyin0g Sort Orders

You can specify a sort order for character fields used in subsequent indexing and sorting operations.

To specify a sort order

From the Tools menu choose Options.
Select the Data tab.
In the Collating sequence box, select the appropriate sort order.

To save this setting for future sessions of Visual FoxPro, choose Set as Default.

Tip You can also specify a sort order with the SET COLLATE TO command or the COLLATE statement in your Config.fpw file. For details about Config.fpw, see Configuring Visual FoxPro.

The current sort order doesn't affect previously created indexes; however, it does affect the results of comparisons and commands such as SEEK and SELECT - SQL.

You can change the sort order at any time. For instance, after opening a customer table you can create index tags representing different sort orders, as shown in the following code. Then you can change the sort order by simply using a different tag:

USE customer
SET COLLATE TO "GENERAL"
INDEX ON fname TAG mygeneral ADDITIVE
SET COLLATE TO "MACHINE"
INDEX ON custid TAG mymachine ADDITIVE
SET COLLATE TO "DUTCH"
INDEX ON lname TAG mydutch ADDITIVE

Note The sort order for an index overrides the current sort order.

The current code page determines which sort orders are available. If you use SET COLLATE to specify a sort order not supported by the current code page, Visual FoxPro generates an error. Also, if you specify a sort order in Config.fpw that isn't supported by the current code page, the sort order defaults to Machine.

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 SCAN FOR...ENDSCAN or LOCATE FOR...CONTINUE 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.