You can use the PARSENAME function as the SQL server doesn’t have the split function. So apart from UDF, you can use this.
SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)
PARSENAME: It takes the string as the first argument and splits it on the period character. It also takes the number as its 2nd argument, and the number specifies which segment of the string to be returned (It works from back to front).