Parsing name field to obtain Last name, first name

[WalterHeisenberg]

Hello,

I am working on a database that simply has a name field. The data has been entered as FirstName LastName.

I am trying to change this to read as: LastName, Firstname but am not sure the best way to proceed.

I assume I could try to find the position of the space and use substring to pull what is to the right of it. Is that the right track?

Any help you could give would be greatly appreciated. I apologize in advance for being a noob to this. Thanks.

 

[SQLSister]

I'm going to tell you what you won't want to hear. There is no simple way to do this. There are likely people in your list with middle names (think Mary Ann) or with spaces in the last name or with Jr., III, etc (many differnt variations) Also name fields tend to have some people who are entered already as last_name, first_name even if they were supposed to be that way or some with no spaces or some with only a first or last name. Yes you are on the right track, but it may take a lot of code to separate out all the possibilities. Make sure you run selects to see what your results are before doing any update.

"NOTHING is more important in a database than integrity." ESquared

 

[chamilz]

This may help you...

http://www.sql-server-helper.com/tips/split-name.aspx

 

[WalterHeisenberg]

Thanks for the input guys. Using Chamilz's link I was able to get this up and running and now I don't have to manually edit this 80,000 records. What a glorious day!

Thanks for your help.