Using replace and concat in MySQL

Question

I have strings values such as the following in a column:

|333|,|331|

I want to perform a balanced string replacement as follows:

xxTM_333_TMxx,xxTM_331_TMxx

I have tried to do this with the REPLACE and CONCAT functions but didn't get the desired output.

For example:

SELECT REPLACE('|333|,|331|','|','xxTM');

This replaces one of the | symbols correctly in each case, but not its matched (balanced) counterpart.

How can I achieve this result in MySQL?

You can't do this with built-in functions in MySQL. The problem is that | means something different for every alternative match. You can do this with a regular expression replace, which MySQL doesn't support natively. Do it in your application. — Jeremy Smyth
– Jeremy Smyth, Commented Sep 10, 2013 at 13:39
select concat(trim(TRAILING 'xxTM' FROM replace(replace('|333|,|331|,|234|','|','xxTM'),'xxTM,xxTM','TMxx,xxTM')),'TMxx'); will output xxTM333TMxx,xxTM331TMxx,xxTM234TMxx — Dell
– Dell, Commented Sep 10, 2013 at 14:01

fthiella · Accepted Answer · 2013-09-10 19:15:24Z

3

SET @st := '|333|,|331|';

SELECT
  CASE WHEN @st LIKE '|%|' THEN
    CONCAT(
      'xxTM',
      REPLACE(REPLACE(@st, '|,|', '_TMxx,xxTM_'), '|', '_'),
      'TMxx')
  END rep_st;

Please see fiddle here.

edited Sep 10, 2013 at 19:15

answered Sep 10, 2013 at 14:13

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

Collectives™ on Stack Overflow

Using replace and concat in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related