MySQL Removing trailing linebreaks from a column

You can replace these directly from SQL by matching "\r" at the end, then replacing that "\r".

Example:

UPDATE Person SET firstName = REPLACE(firstName, '\n', '')
where firstName LIKE '%\n'

or

UPDATE Person SET firstName = REPLACE(firstName, '\r', '')
where firstName LIKE '%\r'

Just ran into this problem, and took care of it like this.

UPDATE table_name SET col_name = REPLACE(TRIM(TRAILING ' ' FROM col_name), 
                                         TRIM(TRAILING '\r' FROM col_name), 
                                         TRIM(TRAILING '\n' FROM col_name))

This will remove new lines (\n), carriage returns (\r), and whitespace.

This will remove all escape characters:

TRIM(TRAILING '\\' FROM (REPLACE(REPLACE(REPLACE(column_name, '\n', ' '), '\r', ' '), '\\', ' ')))

Depending on OS, carriage return will either be "\r\n" or just "\n". It will not be always "\r\n".

The correct way to sanitize this would be to first remove whitespace that would come after the newlines. Then remove newline escape characters then the whitespace that may be present before the newlines.

UPDATE table SET column = TRIM(TRAILING ' ' FROM TRIM(TRAILING '\r' FROM TRIM(TRAILING '\n' FROM TRIM(TRAILING ' ' FROM column))));

This will not work for more complex cases where there are nested trailing spaces and new lines. You will need regular expression replacement for it which is not available in all db versions.

UPDATE table SET column = REGEXP_REPLACE(column, '[ \\r\\n]*$', '');

I use the following queries to replace unwanted characters (spaces, breaks, etc.) from database content with a space.

The SQL's removed the following characters from the "fieldName" field:

1. tabulator \t "CHAR(9)" replaced by spaces "CHAR(32)",
2. new line \n "CHAR(10)" replaced by spaces "CHAR(32)",
3. carriage return \r "CHAR(13)" replaced by spaces "CHAR(32)",
4. double spaces "CHAR(32)+CHAR(32)" replaced by spaces "CHAR(32)",
5. and trimmed at the end!

This ist my favorite:

UPDATE  tableName 
SET     fieldName = TRIM(REGEXP_REPLACE(fieldName, '[[:space:]]+', CHAR(32))) -- the same as '\s+')
WHERE 1;

This runs on thousands of records in milliseconds. :)

First I used the following query which does the same thing:

UPDATE  tableName 
SET     fieldName = TRIM(
                  REPLACE(
                    REPLACE(
                      REPLACE(
                        REPLACE(fieldName, CHAR(9), CHAR(32)), 
                        CHAR(13), CHAR(32)), 
                      CHAR(10), CHAR(32)),
                    CONCAT(CHAR(32), CHAR(32)), CHAR(32))
                  )
WHERE 1;

Then I used the following query which does the same thing: Inspired by Joel Karunungan, the following query does the same:

UPDATE  tableName 
SET     fieldName = TRIM(REGEXP_REPLACE(REGEXP_REPLACE(fieldName, '[\\t\\r\\n]', ' '), '\\s+', ' '))
WHERE 1;

But now the top one runs in milliseconds.

Try something like this:

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

Another vote for Wallack's idea, but more specifically:

Update YOURTABLENAME set YOURCOLUMNNAME = replace(YOURCOLUMNNAME, '\r\n','')

Essentially this should replace carriage returns and breaks with nothing.

Good luck.

Based on EternalHour's answer:

UPDATE TABLE_NAME SET `COLUMN_NAME` = TRIM(TRIM(TRAILING '\r' FROM TRIM(TRAILING '\n' FROM TRIM(`COLUMN_NAME`))))

this one removes trailing \r\n one by one, so either it is a newline or a carriage return or both, it will be stripped, and also removes whitespaces before and after the process.

For simplicity you can assume in most cases than the characters are some combination of whitespace, \r, \n or \r\n so trim them in order:

UPDATE table_name SET col_name = TRIM(TRAILING ' ' FROM TRIM(TRAILING '\r' FROM TRIM(TRAILING '\n' FROM col_name)))