Ways of Extracting the Last Name from a Fully Qualified Name using Sql Server.

DECLARE @FullName VARCHAR(100) = 'Vishal Kumar';
SELECT REVERSE(LEFT(REVERSE(@FullName),
CHARINDEX(' ',REVERSE(@FullName)) - 1)) AS [Last_Name]

SELECT RIGHT(@FullName,
CHARINDEX(' ',REVERSE(@FullName)) - 1) AS [Last_Name]