REPLACE string in MySQL but only specified part

Question

i would like to replace string in my query but only on the end of the string this is my example:

SET @exampleString = 'example_a_chapter_a';

SELECT REPLACE(@exampleString ,'_a','_b1');

Result what I get is this: example_b1_chapter_b1 But i would like this: example_a_chapter_b1

But there can be more time the '_a' in the string as this 'example_a_type_a_chapter_a', but i would like to replace just the end '_a' of the string.

Thanks for you help

Alma Do · Accepted Answer · 2014-03-22 19:55:58Z

2

This will be tricky since MySQL can not replace by regex. One of possible ways is:

SELECT REPLACE(REPLACE(CONCAT(@exampleString, '#END'), '_a#END', '_b1'), '#END', '');

-i.e. add something that 100% is not in original string, to it's end and then replace.

Edit: second REPLACE is needed in case if original string doesn't end with _a (so you'll need to remove added #END)

edited Mar 22, 2014 at 19:55

answered Mar 22, 2014 at 19:32

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bushwacka Over a year ago

In my case there is everytime '_a' on the end

Gordon Linoff · Accepted Answer · 2014-03-22 19:40:52Z

2

You could not use replace() at all. Just reconstruct the string:

select (case when @exampleString like '%_a'
             then concat(left(@exampleString, length(@exampleString) - length('_a')),
                         '_b1'
                        )
             else @exampleString
         end)

This has the advantage that it works even when the string doesn't end in '_a'.

answered Mar 22, 2014 at 19:40

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

REPLACE string in MySQL but only specified part

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related