Replace newline characters in a table

Question

I have a table, with two column: id, text I would like to replace the newline characters to spaces in the text column.

UPDATE txt SET text=REPLACE(text, '\n', ' ') WHERE text LIKE %\n%

Will this work? I want to be sure, before I run this code. I don't like to crash my table, because I have a lot of data in it.

backup the table then test, or create a table with fake data and test, or better yet read the documentation and see what it says `REPLACE` will do, http://dev.mysql.com/doc/refman/5.0/en/replace.html — Patrick Evans, Jul 28 '13 at 09:13
http://stackoverflow.com/questions/1504962/how-to-remove-new-line-characters-from-data-rows-in-mysql — hamilton.lima, Jul 28 '13 at 09:16
*" I want to be sure, before I run this code"* take care that we sometimes have some troll that tell you what to use instead - remember it's the internet. So better look how you can test that your own without loosing anything. That's way better. You will enable yourself to solve all sorts of problems (hence the DV, asking a question that way is not constructive because it's a psychological problem (in the first-hand) of yours, not a programming question). — hakre, Jul 28 '13 at 11:14

score 1 · Accepted Answer · answered Jul 28 '13 at 09:19

First, find at least one row id which has data with \n in it:

SELECT id, text FROM txt
WHERE text LIKE '%\n%'
LIMIT 1

Take a note of id, remember it. Then, try to replace it using your query:

UPDATE txt SET text=REPLACE(text, '\n', ' ')
WHERE id = 123
  AND text LIKE '%\n%'

Now, check if your update was successful (it should show empty result):

SELECT id, text FROM txt
WHERE text LIKE '%\n%'
  AND id = 123

If it worked fine, run the same update, but now without limits.

If anything went wrong, you might have corrupted just one row which id and text you have seen in first step, so it should be easy to fix back.

score 0 · Answer 2 · answered Jul 28 '13 at 09:45

First take a Back up of table txt.

select
*
into tt_txt_2013_07_28
from txt

Now find the count of records where text LIKE %\n%

select count(*)
from txt
where text LIKE %\n%

Note the count

Now you can run the Update Query and match the count. If you have any bad result you can take the backup the records from back-up table tt_txt_2013_07_28

UPDATE txt 
SET text=REPLACE(text, '\n', ' ') 
WHERE text LIKE %\n%

Replace newline characters in a table

2 Answers2