Rearranging text field logically

[JodyAmy]

Situation:

I have a "Name" field from an Oracle 7.3 database in a PeopleSoft 7 environment that is used in a Crystal 5.0 report. The name is currently stored in this format i.e. (AGNES,JOSEPH LEONARD JR or AVALOS,ALFRED JR or BARONOWSKI,GARY WALTER). It is convenient that these three names come up in my query first since they represent three different formatting problems.

The problem is that the name needs to be rearranged in the following order:

JOSEPH LEONARD AGNES JR
ALFRED AVALOS JR
GARY WALTER BARONOWSKI

I was successful in rearranging the names to the correct format. I run into a problem in the instance between the 2nd and 3rd examples. They both have the same number of names (3) since my calculations recognize the "JR" for AVALOS as the second name. In turn, I get one of the two result sets depending on how I change the concatenation of my variables in the formula:

ALFRED AVALOS JR
GARY BARONOWSKI WALTER

or

ALFRED JR AVALOS
GARY WALTER BARONOWSKI

I cannot do a StrLen on the last part of the "Name" and expect it to be two or three characters in length and say that this is my suffix because some last names are two or three characters.

I need to find a way to decipher between the two examples that I'm having problems w/. A database change has about a 0.01% chance of happening, and, even then, it wouldn't happen for months.

Any help would be appreciated!! TIA!!

Jody

 

[kenhamady]

If you were doing it manually, how would you determine that a short string is a title and not a name? What rule do you use? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[JodyAmy]

That's the problem. I don't have a rule. All I know is that the first part of the field up to the "," is the last name. That's the only guidelines that I have. The formula that I created will not account for a person w/ four names only up to three w/ a suffix (if exists). Therefore, a person w/ four names would have the same problems that I described above. I will not be able to know whether it is a title or a name.

I've come to the conclusion that I'm up the proverbial creek w/out the darn paddle!!

Jody

 

[kenhamady]

You aren't going to get a perfect result because there are too many possible variations. Heck, someone's middle name could theoretically be 'jr', and how would you know? Most of the skill in using Crystal is getting around bad data.

That being said, there are probably only a limited number of variations and you might be able to get close by creating some of your own logical rules. You just have to accept that some names might not fit the pattern.

BTW, if you could upgrade to v8 of CR you have more string manipulation tools. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)