How to Replace Newlines in a MySQL String
MySQL databases are supported by blog software, content management systems, desktop applications and other applications. Unfortunately, poor programming sometimes results in data making its way into the database with undesired linebreaks and carriage returns that can interfere with other uses of the data. Cleaning those records up can be done with a MySQL replace query.
Other People Are Reading
Instructions
-
-
1
Open a terminal so you can work from the command line. If you are using phpMyAdmin or another tool that allows you to manipulate the database, open the tool that allows you to enter MySQL commands and skip to Step 4.
-
2
Log in to MySQL by typing "mysql -u <username> -p <password>" and pressing "Enter."
-
-
3
Open the database you want to work with by typing "connect <database name>" and pressing "Enter."
-
4
Instruct MySQL to replace newline characters using the following query:
update <table name> set <field name>=replace(<field name>, "\n", "<replacement string>");
This will replace all instances of the newline character (identified by "\n") in all records of the table you specify. If you wish to limit the records affected, append a "WHERE" statement on the end of the query. See the MySQL documentation for more information on using WHERE.
-
5
Replace undesired carriage returns that often accompany newline characters by running the previous command but using "\r" instead of "\n."
-
1
Tips & Warnings
The best way to deal with newline characters is to prevent them from getting into the database in the first place. If your data is coming from a program or Web app, such as a PHP app, use string manipulation functions to clean up the data before having MySQL write it to the database.
Do not attempt these command unless you have a proficient understanding of MySQL. It is very easy to lose or change data in unintended ways through carelessly entered queries. You should always have a recent backup or data dump of your database before running queries that might affect large numbers of records.
