How to Index an Address Field

MORE INFORMATION

1. On the FoxPro File menu, click New. Then select Program as the file type, and click New.
2. Type or paste the following program into the Untitled.prg window.

        FUNCTION addsort
      PARAMETER xxx
      xxx=ALLTRIM(UPPER(xxx))
      wordnum=OCCURS(' ',xxx)+1  && A space between the quotation marks
      DIMENSION mychar(wordnum)
      mychar=''                  && No spaces between the quotation marks
      yyy=''                     && No spaces between the quotation marks
      x_begin=1
      IF LEN(xxx)>0
      *  The following FOR/NEXT breaks the address down into separate words.
      FOR i=1 TO wordnum
         x_end=AT(' ',xxx,i)-x_begin && A space between the quotation marks
         IF x_end<1                  && No spaces in the string
           x_end=LEN(xxx)
         ENDIF
         mychar(i)=SUBSTR(xxx,x_begin,x_end) && single word in address field
         x_begin=x_begin+x_end+1
         IF x_begin>LEN(xxx)
           i=wordnum
         ENDIF
      ENDFOR
      * The following FOR/ENDFOR  grabs all the character words and
      * build a new string. (1st, 2nd, 3rd, 4th, etc. are considered
      * character words.
      FOR i=1 TO wordnum
         IF ISALPHA(mychar(i))
            yyy=yyy+mychar(i)
         ENDIF
         IF ISDIGIT(mychar(i))
            IF 'ST'$mychar(i) .OR. 'ND'$mychar(i) .OR. 'RD'$mychar(i) .OR. ;
               'TH'$mychar(i)
               yyy=yyy+LEFT('00000000',10-LEN(mychar(i)))+mychar(i)
               mychar(i)=''  && No spaces between the quotation marks
            ENDIF
         ENDIF
      ENDFOR
      * The following FOR/ENDFOR grabs all the numeric words and appends
      * it to the end of the new string.
      FOR i=1 TO wordnum
         IF ISDIGIT(mychar(i)) .AND. .NOT. ;
            (INLIST(mychar(i),'ST','ND','RD','TH'))
            yyy=yyy+LEFT('00000000',8-LEN(mychar(i)))+mychar(i)
         ENDIF
      ENDFOR
   
      ELSE
         Y=" "   && A space between the quotation marks
      ENDIF
      RETURN(yyy)
   						

3. On the FoxPro File menu, click Close. Click Yes when prompted to save changes to untitled.prg. Then type "addsort.prg" without the quotation marks when prompted to "Save As." Then click Save on the "Save As" window.
4. On the FoxPro Window menu, click View and then Open. Select a table to be indexed, and choose Open.
5. Assume the database field to be indexed is called Address. In the Command window type:

         INDEX ON addsort(address) TO myidx