I'm just adding a safety net.

if OBJECT_ID('tempdb.dbo.#names', 'u') is not null drop table #names;

create table #names (

FullName varchar(100));

insert into #names(FullName)

values('Ed Wagner'),

('Georges St. Pierre'),

('John Smith'),

('Madonna');

select FirstName = SUBSTRING(fullname, 1, charindex(' ', fullname + ' ') - 1), --Add a trailing space

LastName = SUBSTRING(fullname, charindex(' ', fullname) + 1, 8000) --8000 will work with any length (except max)

from #names;

drop table #names;