Looking for a scalar function to find the last occurrence of a character in a string

I think what you're looking for is the LOCATE_IN_STRING() scalar function. This is what Info Center has to say if you use a negative start value:

If the value of the integer is less than zero, the search begins at LENGTH(source-string) + start + 1 and continues for each position to the beginning of the string.

Combine that with the LENGTH() and RIGHT() scalar functions, and you can get what you want:

SELECT
    RIGHT(
         FILEPATH
        ,LENGTH(FILEPATH) - LOCATE_IN_STRING(FILEPATH,'/',-1)
    )
FROM FOO