Abaixo um exemplo para separar o nome e o último sobrenome de um campo com o nome completo:
Select substring(nome,1,patindex('% %',nome)) 'Nome',
substring(nome,(len(nome) - (patindex('% %',reverse(nome))))+2,patindex('% %',reverse(nome)) ) 'Sobrenome'
From Funcionarios