MySQL: Search & Replace with target having linefeeds/carriage returns?

Question

I'm needing to cleanup our database and need to mass replace something like this:

<div class="rtecenter"><br />
<font color="#990033"><strong>I'm so hot, I sometimes spontaneously combust.</strong></font><br />
<img src="http://i476.photobucket.com/albums/rr124/redacted/coollogo_com_63811610.gif" alt="" /></div>

(I'm simply going to remove all instances of it).

I already know how to do a mass replace (I'm using PHPMyAdmin), however The problem is that it has CR/LF's in it so I'm not sure how to add the correct string to find in the query ... just for testing, I've tried doing searches using Char(10) or Char(13) where the new lines are but no results are ever returned, (I know they are there though).

Anyone know?

I've tried just \n (no results), just \r (no results) and then \r\n and a ton of results came back but not accurate. — J. Scott Elblein, Feb 21 '12 at 21:19
Are you using a SQL statement to perform the update, or are you using some special feature of PHPMyAdmin? In the former case, please post the statement you have so far; in the latter, please add the "phpmyadmin" tag to your question. — ruakh, Feb 21 '12 at 22:47

score 4 · Accepted Answer · edited Apr 22 '20 at 15:17

If there are only CR/LF's, then you can use this query -

UPDATE table
SET
  column = REPLACE(column, '<div class="rtecenter"><br />\r\n<font color="#990033"><strong>I''m so hot, I sometimes spontaneously combust.</strong></font><br />\r\n<img src="http://i476.photobucket.com/albums/rr124/redacted/coollogo_com_63811610.gif" alt="" /></div>', '');

If there are different line separators, then you can do it in two steps:

UPDATE table
SET
  column = REPLACE(column, '\r', ''); -- replace all \r\n to \n

UPDATE table
SET
  column = REPLACE(column, '<div class="rtecenter"><br />\n<font color="#990033"><strong>I''m so hot, I sometimes spontaneously combust.</strong></font><br />\n<img src="http://i476.photobucket.com/albums/rr124/redacted/coollogo_com_63811610.gif" alt="" /></div>', '');

MySQL: Search & Replace with target having linefeeds/carriage returns?

1 Answers1