Please try the following solution. It is based on XML and XQuery. XML data model is based on ordered sequences. That's why it is so easy to get to the first and last data element.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [NAME] VARCHAR (500));
INSERT INTO @tbl ([NAME]) VALUES
('RAJ A KHOSLA'),
('Peter D Souza'),
('King'),
('Queen'),
(''),
(NULL),
('William Roches Gayle pant');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = SPACE(1);
;WITH rs AS
(
SELECT *
, TRY_CAST('<root><r>' +
REPLACE([name], @separator, '</r><r>') +
'</r></root>' AS XML) AS xmldata
FROM @tbl
)
SELECT *
, xmldata.value('(/root/r[1])[1]', 'VARCHAR(100)') AS FirstName
, xmldata.query('if (count(/root/r) = 1) then <r/> else (/root/r[last()])[1]')
.value('(.)[1]', 'VARCHAR(100)') AS LastName
, xmldata.query('data(/root/r[position() gt 1])')
.value('(.)[1] cast as xs:token?', 'VARCHAR(100)') AS LastName2
FROM rs;