Keeping it short and simple

DECLARE @t TABLE(Fullname varchar(40))
INSERT @t VALUES('John Paul White'),('Peter Smith'),('Thomas')

SELECT
  LEFT(Fullname, LEN(Fullname) - CHARINDEX(' ', REVERSE(FullName))) FirstName,
  STUFF(RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName))),1,1,'') LastName
FROM
  @t

Result:

FirstName  LastName
John Paul  White
Peter      Smith
Thomas     NULL

If you are certain that your names will only ever be two or three words, with single spaces, then we can rely on the base string functions to extract the first and last name components.

SELECT
    CASE WHEN LEN(col) = LEN(REPLACE(col, ' ', '')) + 2
         THEN SUBSTRING(col, 1,
                        CHARINDEX(' ', col, CHARINDEX(' ', col) + 1) - 1)
         ELSE SUBSTRING(col, 1, CHARINDEX(' ', col) - 1)
    END AS first,
    CASE WHEN LEN(col) = LEN(REPLACE(col, ' ', '')) + 2
         THEN SUBSTRING(col,
                        CHARINDEX(' ', col, CHARINDEX(' ', col) + 1) + 1,
                        LEN(col) - CHARINDEX(' ', col, CHARINDEX(' ', col)))
         ELSE SUBSTRING(col,
                        CHARINDEX(' ', col) + 1,
                        LEN(col) - CHARINDEX(' ', col))
    END AS last
FROM yourTable;

Yuck, but it seems to work. My feeling is that you should fix your data model at some point. A more ideal place to scrub your name data would be outside the database, e.g. in Java. Or, better yet, fix the source of your data such that you record proper first and last names from the very beginning.

Demo here:

Rextester

Another option (just for fun) is to use a little XML in concert with an CROSS APPLY

Example

Select FirstName = ltrim(reverse(concat(Pos2,' ',Pos3,' ',Pos4,' ',Pos5)))
      ,LastName  = reverse(Pos1)
 From  YourTable A
 Cross Apply (
                Select Pos1 = xDim.value('/x[1]','varchar(max)')
                      ,Pos2 = xDim.value('/x[2]','varchar(max)')
                      ,Pos3 = xDim.value('/x[3]','varchar(max)')
                      ,Pos4 = xDim.value('/x[4]','varchar(max)')
                      ,Pos5 = xDim.value('/x[5]','varchar(max)')
                From  (Select Cast('<x>' + replace(reverse(A.[Full Name]),' ','</x><x>')+'</x>' as xml) as xDim) XMLData
             ) B

Returns

FirstName               LastName
John Paul               White
Peter                   Smith
Ann Marie               Brown
Jack                    Black
Sam Olaf                Turner
                        Cher
Sally Anne Bella Donna  Baxter

You're trying to do two things at once...I won't solve for you, but here's the direction I'd take:

1. Check this out for string splitting: https://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html . This will allow you to parse the name into a temp table and you can perform your logic on it to create names based on your rules

2. Create this as a table-valued function so that you can return a single row of parsed FirstName, LastName from your parameter. That way you can join to it and include in your results

Have you tried by Using PARSENAME Function?

The last method in splitting a full name into its corresponding first name and last name is the use of the PARSENAME string function, as can be seen from the following script:

DECLARE @FullName VARCHAR(100)
    SET @FullName = 'John White Doe'

    SELECT CONCAT(PARSENAME(REPLACE(@FullName, ' ', '.'), 3),' ',PARSENAME(REPLACE(@FullName, ' ', '.'), 2)) AS [FirstName],
  PARSENAME(REPLACE(@FullName, ' ', '.'), 1) AS [LastName]

For more information, Goto this Site

This is the output..

Make it a table-valued function.

see here for an example

And this is the code you need to create your function. Basically you just need to split your LastName

IF OBJECT_ID(N'dbo.ufnParseName', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufnParseName;
GO
CREATE FUNCTION dbo.ufnParseName(@FullName VARCHAR(300))
RETURNS @retParseName TABLE 
(
-- Columns returned by the function    
FirstName nvarchar(150) NULL,  
LastName nvarchar(50) NULL
)
AS 
-- Returns the spliced last name.
BEGIN

     DECLARE 
        @FirstName nvarchar(250),
        @LastName nvarchar(250);
    -- Get common contact information

    SELECT @LastName = RTRIM(RIGHT(@FullName, CHARINDEX(' ', REVERSE(@FullName)) - 1));
    SELECT @FirstName = LTRIM(RTRIM(Replace(@FullName, @LastName, '')))

     INSERT @retParseName
        SELECT @FirstName, @LastName;

    RETURN;
END

You can run as SELECT * FROM dbo.ufnParseName('M J K');

Why Table-Valued-Function

You can get rid off the duplication of your sql query and achieve DRY

You can try the below query. It is written as per your requirement and it only handles full_name with 2 or 3 parts in it.

;WITH cte AS(
    SELECT full_name, (LEN(full_name) - LEN(REPLACE(full_name, ' ', '')) + 1) AS size FROM @temp
    )

SELECT FirstName = 
    CASE 
        WHEN size=3 THEN PARSENAME(REPLACE(full_name, ' ', '.'), 3) + ' ' + PARSENAME(REPLACE(full_name, ' ', '.'), 2)
        ELSE PARSENAME(REPLACE(full_name, ' ', '.'), 2)
    END,
    PARSENAME(REPLACE(full_name, ' ', '.'), 1) AS LastName
FROM cte

What worked for me was the following:

SUBSTR(Full_Name,1,(LOCATE(Full_Name, ' '))) AS FIRSTTNAME,
SUBSTR(Full_Name,(LOCATE(Full_Name, ' ')+1)) AS LASTNAME