How do i replace multiple keys in mysql

Question

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?

possible duplicates of http://stackoverflow.com/questions/1671040/can-mysql-replace-multiple-characters — jimy, May 04 '11 at 08:08

score 4 · Accepted Answer · answered May 04 '11 at 08:08

4

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.

answered May 04 '11 at 08:08

Johan Soderberg

2,650
1
15
12

Fool proof would be to surround words with spaces `replace(fld1, ' or ' , ' ')` so that you won't match _oral_ and _orb_ – Laas May 04 '11 at 08:11
@KoolKabin I don't know any better way. :D – Johan Soderberg May 04 '11 at 13:43

score 1 · Answer 2 · answered May 04 '11 at 08:07

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.

How do i replace multiple keys in mysql

2 Answers2