Microsoft KB Archive/112839

= How to Index on Numbers in a Character Field =

Article ID: 112839

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft FoxPro 2.5b
 * Microsoft FoxPro 2.5a
 * Microsoft FoxPro 2.5b
 * Microsoft FoxPro 2.0
 * Microsoft FoxPro 2.5b for MS-DOS
 * Microsoft FoxPro 2.5a
 * Microsoft FoxPro 2.5b for MS-DOS
 * Microsoft FoxPro 2.5b for Macintosh

-



This article was previously published under Q112839



SYMPTOMS
If a character field is made up of numbers and the database is indexed on this field, the numbers are not in the expected order.

NOTE: This article assumes that all numbers must be positive.



CAUSE
In character fields, each character is evaluated one at a time from left to right. Therefore, the order may not match that of a numeric field.



RESOLUTION
To get the character field in numerical order, you must create an index key using the PADL function. The PADL function will put a character to the left of the character expression in order to make the digits right aligned.

Example
 In your database, create a character field called IDNUM that has a width of 10 and is composed entirely of numbers.  In the Command window, type: INDEX ON PADL(ALLTRIM(IDNUM),10,"0") TAG IDSORT This command will create a tag call IDSORT that will shift all the characters to the right as far as the length of the field.   In the Command window, issue the following command in order to index the database on the IDSORT tag: SET ORDER TO TAG IDSORT 

The order of IDNUM will now emulate that of a numeric field.



MORE INFORMATION
To illustrate the behavior described in the "Symptoms" section above, suppose you have a character field called IDNUM that has a width of 10 and is made up of all numbers. The following table shows how the numbers will be ordered.   IDNUM order      IDNUM order when table on input        is indexed on IDNUM ---       2                    1        30                   10        1                    12        10                   122        12                   2        200                  200        122                  30 The numbers that begin with "1" will come first, then the numbers that begin with "2", and so on because the index expression is evaluated based on one character at a time rather than on the whole numeric expression.

Additional query words: VFoxWin FoxMac FoxDos FoxWin 1.02

Keywords: KB112839

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.