DOCUMENT:Q101020  25-AUG-1999  [foxpro]
TITLE   :How to Parse One Field Into Two or Three Separate Fields
PRODUCT :Microsoft FoxPro
PROD/VER:2.5x 3.00 | 1.02 2.00 2.5x
OPER/SYS:
KEYWORDS:kbcode

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, version 3.0 
 - Microsoft FoxPro for Windows, version 2.5x 
 - Microsoft FoxPro for MS-DOS, versions 1.02, 2.0, 2.5x 
-------------------------------------------------------------------------------

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:

1. 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.

2. 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)


3. 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.
(c) Microsoft Corporation 1977, All Rights Reserved.
Contributions by Perry Newton, Microsoft Corporation


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

======================================================================
Keywords          : kbcode 
Technology        : kbVFPsearch kbAudDeveloper kbFoxproSearch kbZNotKeyword3 kbFoxPro200DOS kbVFP300
Version           : 2.5x 3.00 | 1.02 2.00 2.5x

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1999.