[dba-SQLServer] Find the second occurrence of a character in a string

Assume these two values in two rows in the table of interest:

Andrei Pascal
Arthur B. Fuller

I need to parse out the surname. I can't just grab the characters following the first space, unless there is only one space. But if there are two spaces, then I need the trailing string following the second space.

CHARINDEX( 'f', 'abcdefgh' ) returns 6  so that part is easy. How to find the second occurrence, if any? In fact, assuming really bizarre data, what I want is the string following the last space in the value.

Even that may not work, given Portuguese and Spanish and French surnames, but for now I will live with that.

Suppose an entry such as:

Juan Carlos de la Vega

I need to grab the string following the last space, in T-SQL syntax.

Any ideas?
TIA,
Arthur