Knowledge Base

How to Parse One Field Into Two or Three Separate Fields

Article ID: 101020

Article Last Modified on 10/15/2003

APPLIES TO

Microsoft Visual FoxPro 3.0 Standard Edition
Microsoft FoxPro 2.0

This article was previously published under Q101020

SUMMARY

The procedure below describes how to parse the contents of one field into two or three separate fields. For demonstration purposes, the CONTACT field in the TUTORIAL\CUSTOMER.DBF database, which contains 500 records, is used as an example. The CONTACT field contains both the first and last names of the contact person.

MORE INFORMATION

IMPORTANT: Prior to issuing commands that will alter the structure of the original database, we recommend that a second or backup copy of the database be created. For this example, use the following commands. (Note that this sample subdirectory structure is a generic one that may not match the one installed on your computer.)

   SET DEFAULT TO C:\<FoxPro_directory>\TUTORIAL
   USE Customer
   COPY TO cust2
   USE cust2

Parsing Data in the CONTACT Field

To parse the data in the CONTACT field, do the following:

Modify the structure of the database by adding two fields, named FIRST and LAST in this example, that are of sufficient width to contain the desired data without truncating it.
Copy the first name in the current record's CONTACT field (up to the space [' '] between the actual first and last name) into the field named FIRST by issuing the following command:
```
      REPLACE first WITH SUBSTR(contact,1,AT(' ',contact)-1)
```
Copy the current record's CONTACT field last name (from the occurrence of the last space to the right) by issuing the following command:

      REPLACE last WITH SUBSTR(contact,RAT(' ',ALLTRIM(contact))+1)

Parsing One Field Into Two Fields

To parse the CONTACT field from all the records in the database into the corresponding record's FIRST and LAST fields, issue the following commands with the scope of ALL included:

   REPLACE ALL first WITH SUBSTR(contact,1,AT(' ',contact)-1)
   REPLACE ALL last WITH SUBSTR(contact,RAT(' ',ALLTRIM(contact))+1)

Parsing One Field Into Three Fields

To parse one field with the following format "Lname, Fname Mname" into three fields, use the following code sample. The REPLACE command could also be used in the following example.

   GO TOP
   SCAN
      SCATTER MEMVAR
      IF ATC(",",m.name) > 0    && checks to see if there is a comma
         *                            between the last and first name
         m.lname = LEFT(m.name,ATC(",",m.name)-1)
      ELSE
         m.lname = LEFT(m.name,ATC(" ",m.name)-1)
      ENDIF

      * gets the first name
      m.fname = SUBSTR(m.name,ATC(" ",m.name)+1,;
         (ATC(" ",m.name,2)-(ATC(" ",m.name)+1)))

      *gets the middle name or initial
      m.mname = ALLTRIM(SUBSTR(m.name,ATC(" ",m.name,2)+1))

      GATHER MEMVAR
   ENDSCAN

REFERENCES

For more information about the ALLTRIM(), AT(), ATC(), RAT(), and SUBSTR() functions, see the FoxPro version 1.02 and version 2.0 "Commands & Functions" manuals and the FoxPro 2.5 "Language Reference" manual.

Additional query words: VFoxWin FoxDos FoxWin 2.50 2.50a 2.50b

Keywords: kbcode KB101020

Microsoft KB Archive/101020

Contents