How to Index on Numbers in a Character Field (112839)
The information in this article applies to:
- Microsoft Visual FoxPro for Windows 3.0
- Microsoft FoxPro for Windows 2.5
- Microsoft FoxPro for Windows 2.5a
- Microsoft FoxPro for Windows 2.5b
- Microsoft FoxPro for MS-DOS 2.0
- Microsoft FoxPro for MS-DOS 2.5
- Microsoft FoxPro for MS-DOS 2.5a
- Microsoft FoxPro for MS-DOS 2.5b
- Microsoft FoxPro for Macintosh 2.5b
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.
Modification Type: | Major | Last Reviewed: | 12/3/2003 |
---|
Keywords: | KB112839 kbAudDeveloper |
---|
|