What are New Line Feed and Carriage Return in a MySQL query?

• Tweet

• Tweet

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.