MySQL – Removing linefeeds from text in a database

admin

April 20, 2012

[adsense id=”0514458240″ width=”468″ height=”60″]

Imagine you have imported data into the database and then find out you have also imported the line feeds and want to remove these. This can be done using an UPDATE statement with the REPLACE function:

In our example here we have a table called “enter” with a single field “string”. The “string” field contains some text:

mysql> select * from enter;
+-------------------+
| string            |
+-------------------+
| This is a test 1
 | 
| This is a test 2
 | 
+-------------------+
2 rows in set (0.00 sec)

Now we need to issue the UPDATE statement using the REPLACE function. In MySQL the set of characters “\n” represents the linefeed character. Carriage return is represented by the “\r”.

mysql> update enter set string = replace(string, "\n", "");
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from enter;
+------------------+
| string           |
+------------------+
| This is a test 1 | 
| This is a test 2 | 
+------------------+
2 rows in set (0.00 sec)

This function could have worked equally well for DOS type text where both the carriage return and line feed are present by changing the REPLACE to include the “\r” in addition to the linefeed character as follows:

UPDATE enter SET string = REPLACE(string, "\r\n", "");

The order in which the carriage return line feed characters appear in the REPLACE function is important as it will search for all entries having carriage return line feed and not line feed carriage return.

MySQL