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

Question

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

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

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.

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

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.

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

trim(both char(13) from fieldname)

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