Splitting Name into Last, First, MI and Suffix
Seyed_GMay 19 2011 — edited May 19 2011All,
I am working on loading the content of a DB2 view into an Oracle 10g table. I am having problems with splitting the employee name field into last name, first name and middle initial. Because the input field for storing name on the Mainframe is a free form field, there are a variety of combinations of names. The only thing that I believe is consistent is that Last name is always first and then first name, middle or middle initial or suffix after that. So far, I have identified 11 different combinations of last name, first name, middle initial and suffix. What is the best way to split the name into its respective last name, first name, middle initial and suffix columns? I have attempted several coding changes to split the names, but I believe the result at the best is %85-90 accurate. Here are some of the combinations of names that I have found so far:
DOE,JOHN D
DOE JOHN DAVID
DOE, JOHN D.
DOE, JOHN JR
DOE, JOHN D JR
DOE, JOHN D. JR
DOE JR, JOHN D
DOE, JOHN D.JR
DOE, JOHN, JR.
Does anyone have any suggestions how to split the name? If it is not possible to foresee every possible scenario and code for it, then I will load the name into a single name column instead of splitting it.
Thank you,
Seyed