What method is used to remove newlines from the mysql data?

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

http://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

What method is used to remove newlines from the MySQL data Python?

How do I find line breaks in MySQL?

How do I trim a space in MySQL?

How do you break a line in a database?