CODEWITHSUNDEEP
mysql
Jay Julian Payne
Jay Julian Payne

Reputation: 780

Removing carriage returns in Mysql DB

I'm trying to replace junk in my DB:

UPDATE xxxxxx set body = replace(body,'<p></p><p>','<p>')

Some tags are not getting replaced because there are line breaks between them...

In phpmyadmin I see this:

yadda yadda<p></p>
<p>yadda yadda

This didn't work..

UPDATE xxxxxx set body = replace(body,'\\r\\n','');
UPDATE xxxxxx set body = replace(body,'\\r','');
UPDATE xxxxxx set body = replace(body,'\\r','');

WHERE ARE THE BREAKS COMING FROM??

Any ideas?

Upvotes: 24

Views: 45263

Answers (2)

nads
nads

Reputation: 517

Neither of these worked for me. Then I realized I also had paragraph breaks ¶. This query worked for me:

UPDATE xxxxxx SET body = REPLACE(REPLACE(body, '\r', ''), '\n', '');

Upvotes: 28

LiamB
LiamB

Reputation: 18606

UPDATE xxxxxx set body = replace(body,'\r\n','');
UPDATE xxxxxx set body = replace(body,'\n','');

Try the above.

Upvotes: 41

Related Questions