CODEWITHSUNDEEP
mysqlreplace
jamjam
jamjam

Reputation: 379

MYSql multiple replace query

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

Upvotes: 30

Views: 42640

Answers (2)

Konchog
Konchog

Reputation: 2188

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;

Upvotes: 0

zerkms
zerkms

Reputation: 254886

Matryoshka-way ;-)

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

Upvotes: 71

Related Questions