rmarda code works fine if you just have first and last name. If you a complete name you need something like this

SELECT LEFT(fullname,CHARINDEX(' ', fullname, 1) - 1) AS fname,

right(fullname,CHARINDEX(' ', REVERSE(fullname), 1) - 1) AS Lname

FROM tablename