Does the MySQL TRIM function not trim line breaks or carriage returns?

Question

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.

Answer 1

select trim(both '\n' from FIELDNAME) from TABLE;

Answer 2

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.

Answer 3

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

Answer 4

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

trim(both char(13) from fieldname)

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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.