How to trim a string by finding multiple words

Question

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?

Answer 1

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.

Answer 2

SELECT COALESCE(TRIM(TRAILING slacks.slack FROM source.src_field), source.src_field) trimmed_field
FROM source
LEFT JOIN slacks ON source.src_field LIKE CONCAT('%', slacks.slack)

How does this work in my case?

You tell only one slack must be trimmed. So the values table and the slacks table are joined by the slack presence. Only one record from slacks can match to any value record. Thus the value and the slack which present in it occurs in one combined record, and trimming is performed. But it is possible when no slack in a value. In that case trimming will give NULL. Wrapping COALESCE() replaces this null with original value. That's all.

Answer 3

You could create a table (t_replace) with values to search for. It could just contain a single column (we can call it "replace_val")

Inside your SP, you could do something like this:

begin
    -- create variables to detect loop completed and hold current replace value
        declare v_finished int;
        declare v_replace;

    -- get replace values
        declare cur1 cursor for
         select `replace_val`
         from
         t_replace;


    declare continue handler
        for not found set v_finished = 1;
    open cur1;

        loop1: loop

            -- get current replace value
                fetch cur1 into v_replace;
                if v_finished = 1 then
                    leave loop1;
                end if;

            if v_sport_id IN (1, 121225) then 

               -- perform replace 
                   set @sqlx = concat('SELECT TRIM(TRAILING \'', v_replace, '\' FROM , v_selection_name) INTO v_selection_name');            
                   select @sqlx;

                   prepare s from @sqlx;
                   execute s;
                   deallocate prepare s;

            end if;
        end loop loop1;

    close cur1;
end;

Please test this on a test version first! I hope it works for you.