Need to separate a concatenated field in SQL 1

[newmanje]

Hi,

I have just imported some data that includes a field for an applicant's name. The field has already concatenated a first name, middle initials and a surname.

I need to extract this to two fields - FirstandMiddleName and Surname (the first field to include any spaces and middle initials if they exist and the surname to only include everything to the right of the final space. Unfortunately I can't figure it out at all.

Please let me know if this is not enough info.

Regards,

Jim

 

[SQLSister]

You can look at patindex or charindex functions to do some of what you like, but your problem is probably much worse than you realize as last names are not always what occurs after the last space. you may have names like
Harry Smith Jr. in there or Mary Ann Jones, M.D.

And if as little thought was given to data validation as a I suspect, you may have names like Cher or Johnson, Jerry.

 

[newmanje]

Thanks SQLSister, I've just taken a look at a sample of the data and can see that there are also a number of records where it has listed surname followed by firstname then records where firstname is followed by firstname.

I suspect I am going to have to go through this manually.