CODEWITHSUNDEEP
mysql
Jeets
Jeets

Reputation: 3377

MySQL Removing trailing linebreaks from a column

I want to remove trailing line breaks from my MySQL column. trim() only removes whitespaces but I also want to remove trailing linebreaks. Could anyone suggest?

Upvotes: 8

Views: 33461

Answers (9)

Vondy
Vondy

Reputation: 11

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.

Upvotes: 1

user19924175
user19924175

Reputation: 1

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

Upvotes: 0

JNAK
JNAK

Reputation: 291

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]*$', '');

Upvotes: 1

Srod
Srod

Reputation: 64

This will remove all escape characters:

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

Upvotes: 2

robotik
robotik

Reputation: 2017

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.

Upvotes: 0

EternalHour
EternalHour

Reputation: 8681

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.

Upvotes: 3

Heres2u
Heres2u

Reputation: 403

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.

Upvotes: 0

jakob
jakob

Reputation: 6005

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'

Upvotes: 12

Wallack
Wallack

Reputation: 792

Try something like this:

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

Upvotes: 0

Related Questions