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?

  • What version of MySQL are you using? See REGEXP_REPLACE() in 8.0
    – Rick James
    Jun 13, 2019 at 15:53
  • Mysql 5.6 I believe
    – Fury
    Jun 13, 2019 at 16:05
  • One approach would be to create a table with the values to search for, and then select from it and use a cursor to loop through the items.
    – raphael75
    Jun 14, 2019 at 17:58

3 Answers 3


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:

        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

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

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.


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:

    -- 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`

    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;

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.