30

From my experiments, it does not appear to do so. If this is indeed true, what is the best method for removing line breaks? I'm currently experimenting with the parameters that TRIM accepts of the character to remove, starting with trimming \n and \r.

Thomas Owens
  • 114,398
  • 98
  • 311
  • 431

10 Answers10

47

My line breaks were in the middle of the string, and I didn't have control over the source data. The following mysql command worked for me:

REPLACE(FIELD,'\r\n',' ')
23

Yes, Trim() will work in MySQL. You have two choices.

1) select it out: 

select trim(BOTH '\n' from [field_name]) as field

If that doesn't work, try '\r', if that doesn't work, try '\n\r'.

2) replace the bad data in your table with an update...

update [table_name] set [field_name] = trim(BOTH '\n' from [field_name])

I recommend a select first to determine which line break you have (\r or \n).

Jérôme Verstrynge
  • 57,710
  • 92
  • 283
  • 453
Lucas
  • 469
  • 3
  • 7
10

The standard MySQL trim function is not like trim functions in any other languages I know as it only removes exact string matches, rather than any characters in the string. This stored function is more like a normal trim you'd find in PHP, or strip in python etc.

CREATE FUNCTION `multiTrim`(string varchar(1023),remove varchar(63)) RETURNS varchar(1023) CHARSET utf8
BEGIN
  -- Remove trailing chars
  WHILE length(string)>0 and remove LIKE concat('%',substring(string,-1),'%') DO
    set string = substring(string,1,length(string)-1);
  END WHILE;

  -- Remove leading chars
  WHILE length(string)>0 and remove LIKE concat('%',left(string,1),'%') DO
    set string = substring(string,2);
  END WHILE;

  RETURN string;
END;

You should then be able to do:

select multiTrim(string,"\r\n\t ");

and it should remove all newlines, tabs and spaces.

rjmunro
  • 27,203
  • 20
  • 110
  • 132
  • I'm in a hurry and I have no time to properly test or analyze the original function so I won't directly edit your answer, but when the string initially consists of several white space, line breaks and tabs **only**, original string is returned. I finally added `IF string REGEXP '^[[:space:]]*$' THEN set string = ''; ELSE ...` at the beginning of the function. What do you think? Are you able to reproduce? – Áxel Costas Pena Feb 28 '13 at 10:05
  • 1
    @Áxel The problem is that mysql sees empty strings the same as all whitespace strings i.e. `"" = " "` returns true! So my `while string<>""` which is supposed to catch the last character of the string being removed actually causes the whole thing to be skipped if the string is all whitespace. – rjmunro Feb 28 '13 at 10:28
  • @Áxel I've fixed it by testing the length of the string, rather than equality with "". – rjmunro Feb 28 '13 at 10:46
  • I can't give credit to it. Good point @rjmunro, and lots of thanks. – Áxel Costas Pena Mar 01 '13 at 10:44
  • This multitrim function doesn't seem to work when string contains utf8 special characters like 'ë' '€' etc. – Kirk Olson Apr 03 '14 at 10:57
8

select trim(both '\r\n' from FIELDNAME) from TABLE; should work if select trim(both '\n' from FIELDNAME) from TABLE; doesn't work.

Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
5

Trim() in MySQL only removes spaces.

I don't believe there is a built-in way to remove all kinds of trailing and leading whitespace in MySQL, unless you repeatedly use Trim().

I suggest you use another language to clean up your current data and simply make sure your inputs are sanitized from now on.

Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
Peter Crabtree
  • 899
  • 6
  • 5
  • 1
    Yes, new inputs are sanitized, but dealing with old data is...well...not fun. – Thomas Owens Nov 11 '08 at 18:36
  • 1
    Ha, of course. But if you've got a list of fields which must not have trailing or leading whitespace, you should be able to write a one-off program that will fix up your current data -- 99% of programming languages have a Trim() that will do what you're looking for. – Peter Crabtree Nov 11 '08 at 18:41
  • 1
    **THIS IS NOT THE CORRECT ANSWER**. Use `trim(both char(10) from content)` and/or `trim(both char(13) from content)` as several people below have mentioned. – MikeSchinkel Mar 05 '20 at 10:32
5

i could only get it to work by making the char;

trim(both char(13) from fieldname)
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
4
select trim(both '\n' from FIELDNAME) from TABLE;
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
LeppyR64
  • 5,251
  • 2
  • 30
  • 35
0

The answers above, when combined, work. A full example of replacing linebreaks at the beginning and end of the field looks like this:

UPDATE table SET field=REPLACE(field, field, TRIM(BOTH '\r\n' FROM field))
Ilya
  • 61
  • 5
0

I faced the same issue with one of the fields. There is no perfect solution. In my case i was lucky that the length of the field was supposed to be 6. So i used a query like

update events set eventuniqueid = substring(eventuniqueid, 1, 6) where length(eventuniqueid) = 7;

You will just have to choose the best option based on your need. The replace '\n' and '\r\n' did not work for me and just ended up wasting my time.

Punit Raizada
  • 494
  • 6
  • 11
-1

REPLACE(FIELD,'\r\n',' ') works perfectly on MySql 5.1 database

Brian Webster
  • 30,033
  • 48
  • 152
  • 225
boardtc
  • 1,304
  • 1
  • 15
  • 20