Replacing Unwanted Characters in MySQL Field

I recently ran into an issue where and automated import wasn’t clearing newline characters before inserting entries into my database. This resulted in thousands of entries in the database containing unwanted newlines in certain fields. To resolve this issue I could have looped through all the entries with a PHP script, but this was a one-time fix (I corrected the issue in the import so it won’t happen again) and I didn’t want to write what would be a resource-consuming looping script in PHP.

What’s the solution, then? Using MySQL’s built-in REPLACE() function. It works like this:

UPDATE table SET field = REPLACE(field, “\n”, “”) WHERE id > “10201”;

Simple but effective. The REPLACE() function takes three parameters:

  1. The field to be searched.
  2. The string to be replaced.
  3. The replacement string.

In my case, I’m searching for the string “\n” in the field “field” and I’m replacing it with “” (nothing). I’m also restricting the replacements to only those rows with an id greater than 10201 since that’s where the import began to experience it’s issue. Why make MySQL do more work than it has to?

So, that’s the solution to this issue.

Leave a Reply