Split first middle and last names from full name from the data base table

CREATE FUNCTION STRING_SPLIT (@STR NVARCHAR(MAX), @DEL NCHAR(1) = ' ')
RETURNS
	@RV TABLE (VALUE NVARCHAR (MAX))
AS
BEGIN

 DECLARE @PART NVARCHAR(MAX)
 DECLARE @POS INT

 WHILE CHARINDEX(@DEL, @STR) > 0
 BEGIN
  SELECT @POS  = CHARINDEX(@DEL, @STR)  
  SELECT @PART = SUBSTRING(@STR, 1, @POS - 1)

  INSERT INTO @RV 
  SELECT @PART

  SELECT @STR = SUBSTRING(@STR, @POS + 1, LEN(@STR) - @POS)
 END

 INSERT INTO @RV
 SELECT @STR

 RETURN
END

DECLARE @tmp TABLE(eid INT IDENTITY(1,1), ename VARCHAR(30))

INSERT INTO @tmp (ename)
VALUES('kula sekhar reddy'),
('sachin ramesh tendulker'),
('mahindra singh dhoni'),
('ravi chandra aswin')


;WITH CTE AS 
(
	--init query
	SELECT 1 AS MyCounter, eid, LEFT(ename, CHARINDEX(' ', ename)-1) AS PartOfName, RIGHT(ename, LEN(ename) - CHARINDEX(' ', ename)) AS Remainder
	FROM @tmp 
	WHERE CHARINDEX(' ', ename)>0
	UNION ALL
	--loop
	SELECT MyCounter + 1 AS MyCounter, eid, LEFT(Remainder, CHARINDEX(' ', Remainder)-1) AS PartOfName, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(' ', Remainder)) AS Remainder
	FROM CTE 
	WHERE CHARINDEX(' ', Remainder)>0
	UNION ALL
	--final
	SELECT MyCounter + 1 AS MyCounter, eid, Remainder AS PartOfName , NULL AS Remainder
	FROM CTE 
	WHERE CHARINDEX(' ', Remainder)=0
) 
SELECT eid, [1] AS FirstName, [2] As SecondName, [3] AS LastName
FROM (
	SELECT eid, MyCounter, PartOfName
	FROM CTE
	) AS dt 
PIVOT(MAX(PartOfName) FOR MyCounter IN ([1], [2], [3])) AS pvt

eid	FirstName	SecondName	LastName
1	kula		sekhar		reddy
2	sachin		ramesh		tendulker
3	mahindra	singh		dhoni
4	ravi		chandra		aswin