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]
V G
18.6k6 gold badges49 silver badges87 bronze badges
asked Oct 1, 2009 at 16:19
4
UPDATE test SET log = REPLACE[REPLACE[log, '\r', ''], '\n', ''];
worked for me.
while its similar, it'll also get rid of \r\n
//lists.mysql.com/mysql/182689
answered Jul 24, 2012 at 8:30
Łukasz RysiakŁukasz Rysiak
2,7901 gold badge21 silver badges19 bronze badges
4
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]
John M
13.7k29 gold badges89 silver badges139 bronze badges
answered Oct 1, 2009 at 16:23
longnecklongneck
11.6k2 gold badges35 silver badges43 bronze badges
2
1] Replace all new line and tab characters with spaces.
2] Remove all leading and trailing spaces.
UPDATE mytable SET `title` = TRIM[REPLACE[REPLACE[REPLACE[`title`, '\n', ' '], '\r', ' '], '\t', ' ']];
answered Dec 11, 2013 at 18:54
GeoGeo
12.4k4 gold badges37 silver badges55 bronze badges
0
update mytable set title=trim[replace[REPLACE[title,CHAR[13],''],CHAR[10],'']];
Above is working for fine.
Anptk
1,1212 gold badges17 silver badges28 bronze badges
answered Jun 1, 2015 at 8:07
1
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]
answered Jun 17, 2015 at 22:41
UlysnepUlysnep
3854 silver badges12 bronze badges
UPDATE mytable SET title=TRIM[REPLACE[REPLACE[title, "\n", ""], "\t", ""]];
answered Oct 2, 2009 at 10:44
RisseRisse
4831 gold badge4 silver badges8 bronze badges
1
Playing with above answers, this one works for me
REPLACE[REPLACE[column_name , '\n', ''], '\r', '']
answered May 12, 2020 at 22:07
jay_mzirayjay_mziray
3012 silver badges8 bronze badges
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]
answered Feb 13, 2017 at 0:16
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
answered Feb 11, 2020 at 3:36