,@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
------------- ------------- -------------
4 10 6
(1 row affected)
FirstName MiddleName LastName
-------------------- -------------------- --------------------
Ram Gopal Varma
(1 row affected)