Mysql – How to trim a string by finding multiple words

MySQLstring manipulation

I want to trim a string if it contains certain words or characters. This is just part of a big stored procedure.

I want if v_selection_name contains any of those characters .eg: ' (RES)' or ' (R)' remove these characters.

For example :

`Micheal Jackson (RES)`    => SHOULD BE =>  `Micheal Jackson`

The way I currently do it is this and it is working:

if v_sport_id IN (1, 121225) then 
    -- remove word reserved from selection name to make match possible
   SELECT TRIM(TRAILING ' (NEEDS SUPPLEMENTING)' FROM v_selection_name) INTO v_selection_name;      
   SELECT TRIM(TRAILING ' (RES)' FROM v_selection_name) INTO v_selection_name;
   SELECT TRIM(TRAILING ' (R)' FROM v_selection_name) INTO v_selection_name;
   SELECT TRIM(TRAILING ' N/R' FROM v_selection_name) INTO v_selection_name;
   SELECT TRIM(TRAILING ' %' FROM v_selection_name) INTO v_selection_name;
end if;

Is there any other better way to do this?

The string will only ever contain one substring to be trimmed.

Does only one substring to be trimmed is contained in a field/variable value? Or there are values like 'something (R) (RES)' which must be trimmed twice?

Best Answer

Replacing multiple substrings is fairly easy to do with REPLACE commands inside of a CASE block.

I successfully tested the following command on MySQL 5.6:

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;

Shameless plug: I tested this command across multiple databases and posted about it on my blog here: How to replace multiple substrings with SQL.

Related Question