using this syntax how do i make it give me the result with other columns eg firstname, lastname, address, zipcode.
also how do i get the middle initial.

e.g vivian A Mimi

select firstname = substring(yourcolumn , 1, charindex(' ',yourcolumn) -1 ),
lastname = ltrim(substring(yourcolumn,charindex(' ',yourcolumn) + 1, len(yourcolumn)- len(substring(yourcolumn, 1, charindex(' ',yourcolumn) -1 ))))
from yourtable