How to Index a Character Field That Contains Digits

ID: Q126620

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

SUMMARY

A table may contain a character field in which the entries are right aligned (right justified) and contain only digits or digits and a non- alphabetic character. To index this field correctly, you must use a combination of functions as detailed in this article.

MORE INFORMATION

An example of such a field is a table of contents field. For example, a table could contain two fields: a table of contents field and a memo field. The memo field is the text itself, and the table of contents field represents the positioning of that text within a document. The following example assumes the Table of Contents field is named TOC and is five characters in size. If the records are entered in random order, you may have five records in which the TOC field has the following values:

   Record 1  "    2"
   Record 2  "  1.1"
   Record 3  "  2.1"
   Record 4  "    1"
   Record 5  "1.1.1"

You can create an index on the TOC field by typing this:

   INDEX ON toc TAG toc

However in the example, this places Record 1 before Record 2. To correctly index a field that has a combination of alphanumerics and non-alphanumerics use this command:

   INDEX ON PADR(ALLTRIM(toc),LEN(toc)) TAG mtoc

Using this command, the Index order for the sample data above will start with Record 4, followed by Record 2 and so on.

Additional reference words: FoxWin FoxDos FoxMac 2.60a KBCategory: kbprg KBSubcategory: FxprgTable

Last Reviewed: June 27, 1995