Create Table Contacts( name varchar(200))

 go

 insert into Contacts(Name) Values('John E. Doe')

 insert into Contacts(Name) Values('Jan Smith')

 insert into Contacts(Name) Values('Ralph Wallace')

 insert into Contacts(Name) Values('Walter C Johnson')

go

select    Left(Name, Len(Name) - Charindex(' ',Reverse(Name),1)-1) As FName

 , Right(Name,Charindex(' ',Reverse(Name),1)-1) As LName

from Contacts