sql server - how to split a column into multiple value using sql -

if have thousands of rows data , find out if column called "last name " contains both first name , last name (could middle initial too). sql command use ? (sql server 2008). once find it, how split last name field , place first name or middle initial in own columns ?

for example:

first name        mi       last name  john              b.       smith                              karen a. jones jane                       lawrence                            joan k. bates 

could done in excel ?

you spaces in last name:

select * t lastname '% %'; 

you rows first name missing:

select * t firstname null or firstname = ''; 

edit:

if assume names "simple" given in sample data, can do:

update t     set firstname = left(lastname, charindex(' ', lastname) - 1),         lastname = right(lastname, charindex(' ', reverse(lastname))),         mi = (case when lastname '% % %'                    rtrim(ltrim(substring(lastname, charindex(' ', lastname + 1), 2)))               end)     lastname '% %' , firstname null; 

this makes lots of assumptions formats . . . there no complete middle names, there no honorics, there no suffixes, there no multiple spaces together. if data simple, should work.

here example of logic in sql fiddle.