split FullName column to FirstName, MiddleName, LastName column in MS SQL

 DECLARE @fullname VARCHAR(60) = 'Ram Gopal Varma'

       ,@FirstName VARCHAR(20)
       ,@MiddleName VARCHAR(20)
       ,@LastName VARCHAR(20)
       ,@1stSpaceIndex INT
       ,@2ndSpaceIndex INT
       ,@3rdSpaceIndex INT
 
SET @1stSpaceIndex = CHARINDEX(' ', @fullname) -- Get 1st Space Index
SET @2ndSpaceIndex = CHARINDEX(' ', @fullname, @1stSpaceIndex + 1) --Get 2nd Space Index with start location
SET @3rdSpaceIndex = CHARINDEX(' ', REVERSE(@fullname)) -- Get 3rd Space Index using reverse fuction
 
--Get 1st name using left function
SET @FirstName = LEFT(@fullname, @1stSpaceIndex - 1) --(-1 remove the space count)
 
--Check 2nd name exists or not
IF @2ndSpaceIndex <> 0
BEGIN
       --Get 2nd name using substring function
       SET @MiddleName = SUBSTRING(@fullname, @1stSpaceIndex + 1, @2ndSpaceIndex - @1stSpaceIndex - 1)
END
 
--Get 3rd name using left & reverse function
SET @LastName = REVERSE(LEFT(REVERSE(@fullname), @3rdSpaceIndex))
 
SELECT @1stSpaceIndex AS '1stSpaceIndex'
       ,@2ndSpaceIndex AS '2ndSpaceIndex'
       ,@3rdSpaceIndex AS '3rdSpaceIndex'
 
SELECT @FirstName AS 'FirstName'
       ,@MiddleName AS 'MiddleName' 

       ,@LastName AS 'LastName' 

Results

1stSpaceIndex 2ndSpaceIndex 3rdSpaceIndex
------------- ------------- -------------
4             10            6
 
(1 row affected)
 
FirstName            MiddleName           LastName
-------------------- -------------------- --------------------
Ram                  Gopal                 Varma

(1 row affected)