This might be useful...

declare @fullname varchar(150),

@space1 int,

@space2 int,

@first varchar(50),

@middle varchar(50),

@Last varchar(50)

set @fullname = 'first last'

select @space1 = CHARINDEX(' ',@fullname)

select @space2 = CHARINDEX(' ',@fullname,@space1+1)

select @first = left(@fullname,@space1-1)

if @space2 > 0

BEGIN

select @Last = right(@fullname,len(@fullname)-@space2)

select @middle = substring(@fullname,@space1+1,@space2-@space1-1)

END

ELSE

BEGIN

select @Last = right(@fullname,len(@fullname)-@space1)

select @middle = ''

END

SELECT @first as FirstName,

@middle as MiddleName,

@Last as LastName