Name separation query

[Terpsfan]

I have to import a table into the database that has a field called Borrower Name. Unfortunately the First name, MI and Last Name are all included in this field. What query can I run to separate this into 3 separate fields?

For Example as it currently stands
John E. Doe is in one field, I want them separated.

 

[JohnDTampaBay]

The short answer is to use CHARINDEX to locate the space within the string and extract each piece from the string based on the location of the space. Everything before the first space is FirstName, between the first and second space is MI, after second space is LastName.

This is fraught with data issues though.[ul][li]What about names without a MI? John Doe[/li]
[li]What about names that start with an initial? J. Edgar Doe[/li]
[li]What about multi-word last names? John E. De La Doe[/li][/ul]
A better answer may be to let someone else to the parsing. There is a website called ListCleanup.com that you can send a table like this and they will send back a table with parsed names. They use some fuzzy logic to target the above issues. If this is a one-shot import and/or its a large table, it may be worth it to let them do it. --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[SriSamp]

You can search for " " (spaces) between the names and then split them based on that. The CHARINDEX function can be used for this.

HTH,
SriSamp