Single FMLSuffix name field to 3 fields: Last, 1st Mid+Sfx 1

[bhfmc]

In CR 8.5, my database has names lists in a single NAME field, First Middle Last Suffix, e.g.,
John Q. Public II
Eve Mary Ellen Smith
Joe Blow
John Carl Normal
I want to create 3 formula fields that would parse this single string into LAST, FIRST & MIDDLE+SUFFIX. This would allow sorting on the LAST, FIRST, MIDDLE+SUFFIX. It would allow the last name to be listed 1st for each person.
One challenge is to identify "Public" as the LAST name and "Q. II" as the MIDDLE+SUFFIX name.
Another is to identify "Mary Ellen" as the MIDDLE+SUFFIX name. Joe Blow's MIDDLE+SUFFIX name should be null.
My output would be:
Blow, Joe
Normal, John Carl
Public, John Q. II
Smith, Eve Mary Ellen
How can I do this?

 

[synapsevampire]

With a tremendous amount of code...

I've been through this many times, and the real problem is that you've fallen victim to a dba who should be back cleaning outhouses. Ultimately you would create 4 distinct fields:

Prefix
First
Last
Suffix

Creating a script on the database to cleanse the data makes good sense as well.

There are so many complications when trying to parse this out, inclusive of typos, that to do a good job against a large database will require a tremendous amount of code.

That said, here's some of the basics in a formula:

whileprintingrecords;
stringvar Prefix:="";
stringvar First:="";
stringvar Last:="";
stringvar Suffix:="";
stringvar Name := {table.name};
// now start the coding
If right(trim(Name),2) in ["Jr", "Sr", "II"] then //etc.
Suffix := right(trim(Name),2);
Name := left(Name,len(trim(name))-2);

Else If right(trim(Name),3) in ["III", "Jr.", "Sr."] then //etc.
Suffix := right(trim(Name),3);
Name := left(Name,len(trim(name))-3);

As you can see, we're just getting started by addressing the suffix, now you have to check for spaces between prefix/first/last, and then hardcode in exceptions, such as:

Oscar De La Hoya
and
Marcus Paul Smith

How do you know where to parse without the names being in a table or the code?

I've written utilities to do this years ago, and there are some canned software applications to assist with this, but of they would be database dependent, which you didn't share.

Nuke the dba and use some simple code to parse the list in SQL or whatever, and then have someone manually go through and edit the rest.

Even then you'll likely have some wrong.

-k