How do I split up the FULLNAME field? 2

[intel2000]

My database contains a fullname, first, middle, and last fields

The problem is the records are only populated with a fullname and I need fox to break it up into the first, middle and last fields

example:

fullname
mark a brown
johanna s smith

the first, middle and last names are seperated by a space and thats it. How can I get fox to do this?

Thanks,
Ross

 

[mspratt]

Hi Ross,

What version of VFP? How many records? Any of the names have a prefix (Dr.) or suffix (Jr. Sr. III).

Regards,

Mike

 

[intel2000]

yes they may or may not have a prefix, first, middle last and suffix.

1.the prefix will never be more the 2 long but may not have one

2.the middle may never be more then 1 long but may not have a middle

3.the first and last name very.

4.the suffix may or may not be there as well but it may be 2 long.

Is this even possible since the table might not always be consistant? What if the lastname has a space in the middle?

I could see many problems where wrong info gets sent to wrong fields.

Thanks,

Ross

 

[mspratt]

Hi Ross,

Something like this:

CLEAR
MyString = "Dr. Michael S. Pratt Jr"
LOCAL MyArray[10]
FOR nCount = 1 TO 10
	IF AT(" ", MyString) > 0
		MyArray[nCount] = LEFT(MyString, AT(" ", MyString)-1)
		MyString = RIGHT(MyString, LEN(MyString) - AT(" ", MyString))
	ELSE
		MyArray[nCount] = MyString
		MyString = "" 
	ENDIF 
	? MyArray[nCount]
	IF EMPTY(MyString)
		EXIT FOR 
	ENDIF 
NEXT nCount
? nCount

You can establish rules according to the word count and have the program pause for human assistance.

Regards,

Mike

P.S. I am neither Dr or Jr :-(

 

[alvechurchdata]

How many records have you got to do? If there are only a couple of hundred and if it's a one-off operation then I'd suggest using the Import Wizard to copy it into a spreadsheet. This will give you one word per column and it will only take a couple of hours to drag and drop them into the right place.

If you do have to automate the process then I'd suggest an algorithm something like:

SCAN
  Break the FullName into separate words
  Get the length of each word
  Look at the pattern of word lengths
  DO CASE
    CASE long, long, 0, 0, 0
      process as FirstName + LastName
    CASE short, long, long, 0, 0
      process as Prefix + FirstName + LastName
    CASE
      ... and so on
ENDSCAN

Then you've got to look for all the exceptions such as "John de Lorean", "Madonna", and "Herr Doktor Otto von Dietrich".

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

Ross,

Keep in mind that no solution will be perfect. You'll always have odd cases where the names don't parse correctly.

That said, if you have VFP 7.0 or above, an easy way of getting the individual elements is to use GETWORDCOUNT() and GETWORDNUM().

Another approach is to use ALINES() to split the name up and put the individual components into an array.

Mike

Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[intel2000]

Thanks I will try these....

 

[TheRambler]

Hi,

Here you have a couple more options:
thread184-899926
thread184-567614

I would try first to download this program written by Walt Kennamer, kept by rgbean and hosted by EBOUGHEY.

Good luck.

 

[RickSchummer]

Mike Feltman wrote an article on parsing names via a class in the February 1999 issue of FoxPro Advisor. If you do not have the issue, you might be able to get back issues from advisor.com.


_RAS

http://www.whitelightcomputing.com

VFP MVP