Parsing a full name field into First,Middle, Last 2

[starbase59]

How do I parse a name field? Formats are inconsistent, some have middle names or initials, some do not.
Examples:
Chris Kimball
Paul Richard Revere
J. Benjamin Franklin

I need a FirstName field, MiddleName field and a LastName field.

I have most of the code, but need help with the Middle name.
SELECT LEFT( [Brokername], PATINDEX( '% %', [Brokername] ) - 1 ) AS FirstName,
SUBSTRING( [Brokername], PATINDEX( '% %', [Brokername] ) + 1, LEN( [Brokername] ) ) AS LastName
FROM table

Thanks in advance.

 

[r937]

no matter how sophisticated your SQL gets, you will ~always~ have to go through the entire table and manually check each result

for example:

Jill St. John -- no middle name

Arland Bruce III -- no middle name





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Right. At one of my prior jobs we did write Names parsing and Address Standardization applications and it was a complex program with also user interface to correct the problems.