How to remove new line characters from data rows in mysql?

mysql trim
TigerTiger picture TigerTiger · Oct 1, 2009 · Viewed 130.9k times · Source

I can loop through all of the rows in a php script and do

UPDATE mytable SET title = "'.trim($row['title']).'" where id = "'.$row['id'].'";

and trim can remove \n

But I was just wondering if something same could be done in one query?

 update mytable SET title = TRIM(title, '\n') where 1=1

will it work? I can then just execute this query without requiring to loop through!

thanks

(PS: I could test it but table is quite large and dont want to mess with data, so just thought if you have tested something like this before)

Answer

Łukasz Rysiak picture Łukasz Rysiak · Jul 24, 2012 
UPDATE test SET log = REPLACE(REPLACE(log, '\r', ''), '\n', '');

worked for me.

while its similar, it'll also get rid of \r\n

http://lists.mysql.com/mysql/182689

Related questions

Does the MySQL TRIM function not trim line breaks or carriage returns?

From my experiments, it does not appear to do so. If this is indeed true, what is the best method for removing line breaks? I'm currently experimenting with the parameters that TRIM accepts of the character to remove, starting with …

Read More
MySQL select fields containing leading or trailing whitespace

I can use the MySQL TRIM() method to cleanup fields containing leading or trailing whitespace with an UPDATE like so: UPDATE Foo SET field = TRIM(field); I would like to actually see the fields this will impact before this is …

Read More
How Do I Trim Leading and Trailing Quote from MySQL Row?

I have a MySQL table that I have imported from a CSV file. In this process, a bunch of the entries have quote marks leading and trailing the entry of several data rows. For example the the table 'example_table' …

Read More