How to remove new line characters from data rows in mysql?
MysqlTrimMysql Problem Overview
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)
Mysql Solutions
Solution 1 - Mysql
UPDATE test SET log = REPLACE(REPLACE(log, '\r', ''), '\n', '');
worked for me.
while its similar, it'll also get rid of \r\n
Solution 2 - Mysql
your syntax is wrong:
update mytable SET title = TRIM(TRAILING '\n' FROM title)
Addition:
If the newline character is at the start of the field:
update mytable SET title = TRIM(LEADING '\n' FROM title)
Solution 3 - Mysql
-
Replace all new line and tab characters with spaces.
-
Remove all leading and trailing spaces.
UPDATE mytable SET
title= TRIM(REPLACE(REPLACE(REPLACE(title, '\n', ' '), '\r', ' '), '\t', ' '));
Solution 4 - Mysql
update mytable set title=trim(replace(REPLACE(title,CHAR(13),''),CHAR(10),''));
Above is working for fine.
Solution 5 - Mysql
Removes trailing returns when importing from Excel. When you execute this, you may receive an error that there is no WHERE; ignore and execute.
UPDATE table_name SET col_name = TRIM(TRAILING '\r' FROM col_name)
Solution 6 - Mysql
UPDATE mytable SET title=TRIM(REPLACE(REPLACE(title, "\n", ""), "\t", ""));
Solution 7 - Mysql
Playing with above answers, this one works for me
REPLACE(REPLACE(column_name , '\n', ''), '\r', '')
Solution 8 - Mysql
My 2 cents.
To get rid of my \n's I needed to do a \\n. Hope that helps someone.
update mytable SET title = TRIM(TRAILING '\\n' FROM title)
Solution 9 - Mysql
For new line characters
UPDATE table_name SET field_name = TRIM(TRAILING '\n' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r\n' FROM field_name);
For all white space characters
UPDATE table_name SET field_name = TRIM(field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\n' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r\n' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\t' FROM field_name);
Read more: MySQL TRIM Function