In MySQL, you'd use LOCATE instead of CHARINDEX. The idea here is that when the name is reversed, the location of the first blank going forward in the reversed name is 1 more than the number of characters that you want to extract from the right of the original, unreversed, name.

The CASE expression covers the situation where the name contains no blank at all, e.g. Cher.

select substr(name ,instr(name,' ',-1)+1 ) as LastName from Table_Name

Here, the -1 parameter of INSTR indicates that we're searching for the first occurrence going backward from the end of the column. SUBSTR usually requires a starting position and a length, but if the length is omitted, the substring extends to the end of the column.

Note that if INSTR finds no blank, it returns 0, but if 0 is the starting position in SUBSTR, it is treated as 1.