You don't say anything about the quality of the name fields. If they're clean, the PARSENAME function is a great approach.

I work with a lot of crappy name & address info we get from clients, and I would be careful assuming it doesn't already have periods that would screw up the PARSENAME approach. Any "Mr." or "Mrs." or "Dr." prefixes in these names?

I've used LEFT(xx,CHARINDEX(' ',xx)-1) to get the first word in a text field. I've also used RIGHT(xx,CHARINDEX(' ',REVERSE(xx))-1) to get the last word. Use "WHERE CHARINDEX(' ',xx) > 0' if you're not sure there is more than one word in the field.

(Mostly to build tables to see what new prefixes and suffixes they've come up with this month)