How to replace multiple substrings with SQL

The CASE command allows you to perform actions based on IF / ELSE logic. When combined with the REPLACE command, it allows you to replace an arbitrary amount of substrings.

-- Works in PostgreSQL, MySQL, SQL Server, and Oracle

select
    case
        when my_string like '%(RES)%' then replace(my_string, '(RES)', '')
        when my_string like '%(R)%' then replace(my_string, '(R)', '')
        else my_string
    end as my_string_without_characters
from
    sample_table;

`sample_table`

my_string
Micheal Jackson (RES)
Tom Brady (R)
Tony Stark

Query results

my_string
Micheal Jackson
Tom Brady
Tony Stark

Explanation

Here we are checking each row for the existence of a substring using the CASE and LIKE commands. If the string contains the subtring we are looking to remove, we use the REPLACE command to replace it with an empty string (effectively deleting it.)