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

Question

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)

You can just get rid of the where clause...be careful w/ your trim though because you might have other characters in the set (like carriage return '\r') — jkelley
– jkelley, Commented Oct 1, 2009 at 16:24
You should have tested that before you ask this question. It works. If you want to test that on big table you can use LIMIT clause. — Lukasz Lysik
– Lukasz Lysik, Commented Oct 1, 2009 at 16:26
@PhillPafford Nothing, technically, it's just redundant. (I know this is >5 years old, but might help other people coming to read it.) — Bing
– Bing, Commented Apr 23, 2015 at 20:10
Some DB/SQL Tools warn you (rightfully!) when you try to execute an UPDATE without a WHERE clause. This where 1=1 looks an awful lot like a way to bypass this check. I would avoid that. @PhillPafford @Bing — LukasKroess
– LukasKroess, Commented Jan 11, 2024 at 8:13

Łukasz Rysiak · Accepted Answer · 2012-07-24 08:30:35Z

159

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

3,1181 gold badge25 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

longneck Over a year ago

this does not remove trailing \n, it removes all \n, which is not desired here.

Mike Purcell Over a year ago

This may not have worked for the exact OP, but did help in a sitch I was dealing with, +1

user3453428 Over a year ago

exactly what I needed.

John M · Accepted Answer · 2020-11-12 17:04:06Z

141

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)

edited Nov 12, 2020 at 17:04

John M

14.7k29 gold badges95 silver badges143 bronze badges

answered Oct 1, 2009 at 16:23

longneck

12.3k3 gold badges39 silver badges49 bronze badges

2 Comments

Jeff Over a year ago

coming from MSSQL, this syntax was very foreign to me. i thought you were using pseudocode! but it worked :)

Mark G Over a year ago

Documentation on TRIM() for reference.

Geo · Accepted Answer · 2014-12-16 21:33:39Z

20

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', ' '));

edited Dec 16, 2014 at 21:33

answered Dec 11, 2013 at 18:54

Geo

13.1k4 gold badges46 silver badges56 bronze badges

Comments

Anptk · Accepted Answer · 2015-06-01 08:51:42Z

12

update mytable set title=trim(replace(REPLACE(title,CHAR(13),''),CHAR(10),''));

Above is working for fine.

edited Jun 1, 2015 at 8:51

Anptk

1,1212 gold badges17 silver badges28 bronze badges

answered Jun 1, 2015 at 8:07

Phaneendra Kasalanati

4085 silver badges9 bronze badges

1 Comment

Mohamed23gharbi Over a year ago

This one is useful when '\n' is not working (tested on mysql workbench)

Ulysnep · Accepted Answer · 2015-06-17 22:41:33Z

8

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

Ulysnep

4055 silver badges12 bronze badges

Comments

jay_mziray · Accepted Answer · 2020-05-12 22:07:24Z

6

Playing with above answers, this one works for me

REPLACE(REPLACE(column_name , '\n', ''), '\r', '')

answered May 12, 2020 at 22:07

jay_mziray

3313 silver badges8 bronze badges

Comments

Risse · Accepted Answer · 2009-10-02 10:44:52Z

3

UPDATE mytable SET title=TRIM(REPLACE(REPLACE(title, "\n", ""), "\t", ""));

answered Oct 2, 2009 at 10:44

Risse

4911 gold badge4 silver badges8 bronze badges

1 Comment

longneck Over a year ago

this does not remove trailing \n, it removes all \n, which is not desired here.

Darshan Montgomery · Accepted Answer · 2017-02-13 00:16:38Z

0

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

Darshan Montgomery

839 bronze badges

Comments

Lue Inubashiri · Accepted Answer · 2020-02-11 03:36:23Z

0

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);

Collectives™ on Stack Overflow

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

9 Answers 9

3 Comments

2 Comments

Comments

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

3 Comments

2 Comments

Comments

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related