How to Index a Field to Force Alpha Sort Before Numeric

ID: Q122745

2.60a   | 2.60a  | 2.60a     | 2.60

WINDOWS | MS-DOS | MACINTOSH | UNIX

The information in this article applies to:

Microsoft FoxPro for Windows, version 2.6a
Microsoft FoxPro for MS-DOS, version 2.6a
Microsoft FoxPro for Macintosh, version 2.6a
Microsoft FoxPro for UNIX, version 2.6

SUMMARY

This article explains how to index a field sorted in ascending order and have alphabetic values listed prior to numeric values -- which is the opposite of what usually happens when values are sorted in ascending order.

MORE INFORMATION

The following code creates an index on the customer table (located in the FoxPro directory and the tutorial subdirectory). The index uses the Address field to list the records in alphabetical ascending order, placing values that start with a number below values that start with a letter.

   SET DEFAULT TO SYS(2004)+"tutorial"
   USE CUSTOMER
   INDEX ON IIF(ISALPHA(ADDRESS),"","|")+ADDRESS TAG TEST
   BROWSE

The IIF expression in the INDEX command places a pipe character (|) in front of any value containing a number as the first digit. The pipe has a higher ASCII value than any alphabetic character, so anything that begins with a pipe is placed after entries that begin with a character. The pipe character is only in the index; it is not in the table (data).

Additional reference words: FoxWin FoxDos FoxMac FoxUnix 2.60 2.60a KBCategory: KBSubcategory: FxprgBrowse

Keywords          : kbcode FxprgBrowse 
Version           : 2.60a | 2.60a | 2.60a | 2
Platform          : MACINTOSH MS-DOS UNIX WINDOWS

Last Reviewed: May 22, 1998