Understanding Sort Orders
Collapse the table of content
Expand the table of content

Understanding Sort Orders

Visual Studio 2005

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.

NoteNote

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"
NoteNote

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

Community Additions

ADD
Show:
© 2016 Microsoft