CODEWITHSUNDEEP
mysqlstringreplace
KoolKabin
KoolKabin

Reputation: 17653

How do i replace multiple keys in mysql

i would like to replace multiple words from a field in mysql table. I have been doing it using multiple steps:

update table1 set fld1=replace(fld1,'and', '');
update table1 set fld1=replace(fld1,'or', '');
update table1 set fld1=replace(fld1,'xor', '');
...

How do i do it single step?

Upvotes: 2

Views: 3496

Answers (2)

Johan Soderberg
Johan Soderberg

Reputation: 2740

Ugly way...

UPDATE table1 SET fld1 = replace(replace(replace(fld1, 'and', ''), 'xor', ''), 'or', '')

Note that if you replace 'or' before 'xor' it will match part of 'xor' and leave the x, so the order is important.

Upvotes: 4

mu is too short
mu is too short

Reputation: 434685

You can compose replace calls:

update table1
set fld1 = replace(replace(replace(fld1, 'xor', ''), 'or', ''), 'and', '');

I think that's the best you can do in MySQL without compiling in extra user-defined functions. PostgreSQL and (AFAIK) Oracle have full regex support but MySQL only supports regex matching.

If you have to do a lot of this sort of thing then you might want to do it outside the database to get a reasonable solution that doesn't involve insane levels of nested replace functions.

Upvotes: 1

Related Questions