Parsing full name in SQL Server 2008 -

i know common question , i've reads posts cannot work here am. have table called publicdata. in table called people , column called name.

in names column strings of names formatted like:

smith, steve smith steve smith, steve andrew smith, steve andrew robin 

what i'd quick , dirty script can run parse string in name column , dump split names firstname, middlename , lastname columns reside in names table.

also... have table (let's named same, names this:

steve smith  steve smith  steve andrew smith jr  steve andrew jackson smith 

both tables not have salutation , have jr, sr, etc... thank in advance everyone...

ps.. please no self contained examples i've seen them around cant them work situation :(

well need split function. split names using suggestion: https://gallery.technet.microsoft.com/scriptcenter/t-sql-script-to-split-a-308206f3 , put of names in own table.

i recommend if data structured last name firstname etc match on length of last name first. take:

select name your_table left(name, length(last_name_from_name_table))=length(lastname_from_name_table) , upper(left(name_from_your_table, length(lastname_from_name_table))=upper(lastname_from_name_table) 

and blow out series of cases capture names want cases first names, jr, etc.