Need help parting out a name column

[FrankW]

I have a table with a "name" column that I need to part out to seperate columns of first name, middle, last name. Such as

John Doe John | | Doe
Mary A Smith Mary | A | Smith
Bob Van Hushen Bob | | Van Hushen
Frank Williams Jr. Frank | | Williams Jr.
Bill R. White Bill | R | White

Is there anyone who could suggest a query that would be able to parse these names out as such? Or is there a function that would allow me to do this. I am using sql 7.

I have been only able to get the first name parted with this query,

substring(name,1,case when charindex(' ',name,1)> 0 then charindex(' ',name,1)-1
else len(name)end) as firstname

Can anyone help with some suggestions as to handle the rest of the name????

Thanks alot!

FrankW

 

[karluk]

I would guess that there's no chance you will be able to successfully parse an arbitrary name without building in a lot of intelligence into your code. Even the examples that you give point out a lot of the difficulties. "Bob Van Hushen" could easily be a {first middle last} combination, but people recognise "Van" as a common beginning to a last name with Germanic roots. However, in a similar situation, "Gary Van McIntyre should parse "Van" as the middle name. The only difference I see is that the last name is Scottish and so isn't likely to include "Van" in the last name.

You should start by parsing the name, breaking it apart whenever you find spaces. Then you will have to go through the parts and apply fairly complex logic to make reasonable judgments as to where to make the breaks between first, middle, and last.

Of course you can simplify your task by limiting the range of names you are willing to deal with. One obvious simplification is to allow only middle initials, not full middle names. Then you can determine whether your second part is a single character or longer. Single characters would be assumed to be middle initials, anything else would be included in the last name.

 

[stanleyrx]

Soln #1 Not a solid answer but you might look into exit routines to format this.
I know IBM MVS has this capability
Soln #2 Pass the report to a file and have a post process program parse it properly