Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

trim

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)

like image 343
TigerTiger Avatar asked Oct 01 '09 16:10

TigerTiger


People also ask

How do I remove special characters from a MySQL query?

You can remove special characters from a database field using REPLACE() function.

What is line break in MySQL?

You can simply replace all \n with <br/> tag so that when page is displayed then it breaks line. UPDATE table SET field = REPLACE(field, '\n', '<br/>')


1 Answers

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

like image 107
Łukasz Rysiak Avatar answered Sep 23 '22 15:09

Łukasz Rysiak