0

I have a MySQL table which has \r\n and \r in the values of some of the fields. The datatype of the column is text. How can I get rid of these returns and new lines from the values?

To clarify, I would like to go from this:

\r\n \r\n This is an example of example text. \r\nThis is an example of more example text. \r\n

To this:

This is an example of example text. This is an example of more example text.

This data was originally pulled from an HTML file. When I attempt to run the following command, it does not work. 

UPDATE tablename SET columnname = REPLACE(columnname, '\r\n', '');
Sagar Samtani
  • 203
  • 1
  • 4
  • 10
  • remove the data in the table itself? or remove the data when displaying it somewhere? ideally, you'd leave the data alone or scrub it of bad data on the way into the database. – ethorn10 Jun 21 '15 at 23:43
  • Using the `UPDATE` query with `REPLACE()` as described in the linked answers, you may need to run multiple permutations since you said you have both `\r\n` and `\r`. First run `\r\n` together, then separately run `\r` alone and `\n` alone if needed. It _can_ be done all at once by nesting multiple `REPLACE()` but it is ugly. Easier to run it 3 times. – Michael Berkowski Jun 21 '15 at 23:46

0 Answers0