MySQL: Mass Replace using a regex?

Question

I'm trying to replace all instances of   with a simple space EXCEPT if it's surrounded by DIV tags. I've tried tinkering with the NOT REGEXP and NOT RLIKE within the replace query, like this:

UPDATE table SET column = replace(column,NOT REGEXP '>&#160;<',' ');

But it gives a syntax error. Anyone have any suggestions?

score 1 · Accepted Answer · answered Feb 25 '12 at 01:55

1

How about three separate updates...

UPDATE table SET column = replace(column, '> <', '%%LOL$$');
UPDATE table SET column = replace(column, ' ', ' ');
UPDATE table SET column = replace(column, '%%LOL$$', '> <');

answered Feb 25 '12 at 01:55

Diego

18,035
5
62
66

score 0 · Answer 2 · answered Feb 25 '12 at 01:21

0

Something like this should work:

UPDATE table SET column = REPLACE(column, '&#160;', ' ') WHERE column NOT LIKE '%div>&#160;</%'

answered Feb 25 '12 at 01:21

Bruno Silva

3,077
18
20

I tried this and it said over 8K rows affected, and when I went in and looked at one of them it all looked the same as before. *eek* lol – J. Scott Elblein Feb 25 '12 at 15:16

MySQL: Mass Replace using a regex?

2 Answers2