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, Oct 01 '09 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, Oct 01 '09 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, Apr 23 '15 at 20:10

score 146 · Answer 1 · answered Jul 24 '12 at 08:30

146

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 '12 at 08:30

Łukasz Rysiak

2,898
1
22
19

8

this does not remove trailing \n, it removes all \n, which is not desired here. – longneck Apr 22 '13 at 14:42
21

This may not have worked for the exact OP, but did help in a sitch I was dealing with, +1 – Mike Purcell Jun 25 '13 at 17:56
exactly what I needed. – user3453428 Nov 24 '16 at 07:31

score 133 · Accepted Answer · edited Nov 12 '20 at 17:04

133

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 '20 at 17:04

John M

14,338
29
91
143

answered Oct 01 '09 at 16:23

longneck

11,938
2
36
44

8

coming from MSSQL, this syntax was very foreign to me. i thought you were using pseudocode! but it worked :) – Jeff Apr 22 '13 at 14:29
Documentation on [TRIM()](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_trim) for reference. – Mark G Mar 27 '19 at 18:06

Geo · Answer 3 · 2014-12-16T21:33:39.650

19

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 '14 at 21:33

answered Dec 11 '13 at 18:54

Geo

12,666
4
40
55

score 11 · Answer 4 · edited Jun 01 '15 at 08:51

11

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

Above is working for fine.

edited Jun 01 '15 at 08:51

Anptk

1,125
2
17
28

answered Jun 01 '15 at 08:07

Phaneendra Kasalanati

398
5
9

3

This one is useful when '\n' is not working (tested on mysql workbench) – Mohamed23gharbi Nov 23 '15 at 16:32

score 8 · Answer 5 · answered Jun 17 '15 at 22:41

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 '15 at 22:41

Ulysnep

395
4
12

score 3 · Answer 6 · answered Oct 02 '09 at 10:44

3

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

answered Oct 02 '09 at 10:44

Risse

493
1
4
8

4

this does not remove trailing \n, it removes all \n, which is not desired here. – longneck Apr 22 '13 at 14:43

score 3 · Answer 7 · answered May 12 '20 at 22:07

3

Playing with above answers, this one works for me

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

answered May 12 '20 at 22:07

jay_mziray

301
2
8

score 0 · Answer 8 · answered Feb 13 '17 at 00:16

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 '17 at 00:16

Darshan Montgomery

83
9

score 0 · Answer 9 · answered Feb 11 '20 at 03:36

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

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

9 Answers9

Linked

Related