MySQL: How to Remove all Newline Characters from a Column

Annoyingly, carriage returns and line-feeds (aka newline characters) often get sucked up into our database through imports, inserts and the like, which is annoying because these generally invisible characters play havoc with simple WHERE COLUMN = STRING statements because what you thought were matching strings are actually not matching at all! However, a once off statement to remove all these annoying chr(10) and chr(13) characters is actually pretty simple to implement.

Using the handy REPLACE MySQL function and taking recognition that MySQL denotes character returns as r and line-feeds as n, we can put together a simply SQL statement that looks like this:

UPDATE `myTable` SET `myColumn` = REPLACE(REPLACE(`myColumn`, 'r', ''), 'n', '');

This will remove all character returns and line-feeds by replacing them with a blank string.

Nifty.