Maybe you could use something like this. The CTE is just to have some sample data to test with.

WITH SampleData(String) AS(

SELECT 'Doe, John, H *^' UNION ALL

SELECT 'Doe, John, H, **' UNION ALL

SELECT 'Doe, John, H *¨#$'

)

SELECT LEFT( String, LEN(String) - PATINDEX( '%[A-Za-z]%', REVERSE(String)) + 1)

FROM SampleData