MYSql multiple replace query

Question

I have this

UPDATE  table 
SET  example = REPLACE(example, '1', 'test') 
WHERE example REGEXP '1$'

So this code replaces all instances of "1" in the "example" field with "test".

I want to repeat this for 2, 3, 4 and so on.

But it would be very inefficient to use separate querys.

Is the any way I can do this with just one query?

Thanks

Sounds like you need to move it to a stored procedure, using a variable. — D.N.
– D.N., Commented Mar 28, 2011 at 14:25

zerkms · Accepted Answer · 2011-03-28 14:25:47Z

71

Matryoshka-way ;-)

REPLACE(REPLACE(REPLACE(example, '3', 'test') , '2', 'test') , '1', 'test')

answered Mar 28, 2011 at 14:25

zerkms

256k73 gold badges447 silver badges551 bronze badges

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

2 Comments

jamjam Over a year ago

Hi It worked. But what about the where clause. Even though it is not necessary. I read it's important to prevent errors.

Dr. House Over a year ago

what if I need to do this for 10 different strings? We should be using a procedure to make that more elegant I assume.

Konchog · Accepted Answer · 2017-05-04 13:09:58Z

0

A stored procedure.

Given you have a table 'lut' with a set of values that you want replacing in a field 'content' from a table is called 'example'

delimiter //
CREATE PROCEDURE myreplace()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE lv CHAR(64);
DECLARE li INT;
DECLARE lut CURSOR FOR SELECT id,value FROM lut l;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN lut;
lut_loop: LOOP
FETCH lut INTO li,lv;
IF done THEN
LEAVE lut_loop;
END IF;
update example set content = replace(content,lv,li);
END LOOP; 
CLOSE lut;
END;
//
delimiter ;
call myreplace();
drop procedure myreplace;

answered May 4, 2017 at 13:09

Konchog

2,2481 gold badge23 silver badges30 bronze badges

2 Comments

zerkms Over a year ago

What is the point to create a SP that only updates a single table? How is it better than an SQL query?

Konchog Over a year ago

@zerkms, the advantage is that one can have as many replace operations as one likes in the lookup table. The example here uses a concrete table name, but it is trivial to pass a tablename and it's corresponding fieldname as parameters.

Collectives™ on Stack Overflow

MYSql multiple replace query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related