Share This Article:

Subscribe to Kavoir:

We all know what a new line is represented in c and in PHP as:

"\n"

And a carriage return:

"\r"

In a SQL / MySQL query, when you want to add new lines or line feeds into the strings, you will need the CHAR() function of MySQL which accepts an ASCII character code as input and returns a character accordingly. For example, to search a specific column for all new lines and erase them:

UPDATE sometable SET somecolumn = REPLACE(somecolumn, CHAR(10), ''); // CHAR(10) is a new line

This could get the thing done in most cases. However in some systems new lines are a combination of a new line and a carriage return, namely "\r\n". In this case, you will need CHAR(13):

UPDATE sometable SET somecolumn = REPLACE(somecolumn, CHAR(13) + CHAR(10), ''); // CHAR(13) is a carriage return and the plus sign is how strings are concatenated in MySQL.

Share This Article:

Subscribe to Kavoir:

You should also read:

• MySQL: Replace Substring with Another String – the MySQL String Replace Function
• MySQL, PHP: Display MySQL table fields and data
• mysql command line character set option for importing SQL files encoded in UTF8
• MySQL: LOAD DATA LOCAL INFILE only imports 1 or 2 rows?
• PHP: Count Lines of a File and Get the Number of Lines in a Text File