This is something that suits to you:
Create FUNCTION [dbo].[fnGetWords](@pStr varchar(max), @pWords int)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @lPos int, @lCtr int
SET @lCtr = Len(@pStr)
SET @lPos = 0
WHILE @lCtr>=@pWords
BEGIN
SET @lPos = CHARINDEX(' ', @pStr, @lPos + 1)
SET @lCtr = @lCtr - 1
END
RETURN SUBSTRING(@pStr,@lPos, Len(@pStr)-1)
END
select dbo.fnGetWords('This is getting words from right',-2)
gives the last word "right". you can change the logic as per your criteria.