If you are on SQL 2005, you can still do this in C# or VB with help of
a CLR function.

While never is a fun exercise, a 3GL language is likely to be more
efficient, both in terms of execution and development.

Then again, do this in T-SQL? Well, first a table-valued function that
splits up the string on spaces (look at my web site for such functions:
http://www.sommarskog.se/arrays-in-sql.html) Use a function that also
returns the list position. Inserts all parts into a table, keep the list
position. Then you have a table that defines strings to ignore: "Jr", "and"
and so on. Delete such ignorable strings. Also delete all strings that
are to short to be a last name.

Use the row_number function to number the remainging rows from 1 starting
with the row with the highest list position. Assume now that strings 1, 3, 5
and so one are the last names.

In any case, you should use the language you are the most comfortable with,
because it will take you long time, and several reiterations until you
have something which is working good enough. It will never be exact.