How to replace last occurrence of a substring in MYSQL?

This is shorter and more readable:

SELECT TRIM(TRAILING 'Corp' FROM 'American Corp National Corp')

Try:

select reverse(concat(
               left(reverse('American Corp National Corp'),
                    instr(reverse('American Corp National Corp'),reverse('Corp'))-1),
               substr(reverse('American Corp National Corp'),
                      instr(reverse('American Corp National Corp'),reverse('Corp'))+
                      length('Corp')))) result

(SQLFiddle)