MySQL: Line breaks in MySQL

The sign for a line break in PHP and several other languages is \n, the sign for a carriage return is \r. Depending on the system thus \r\n or just \n is a newline, while sometimes also the \n implicitly stands for \r\n.

But what about MySQL? How can we explicitly look for line breaks, for example, to replace a line break with another string.

To specify a line break in MySQL, we need the function CHAR(). CHAR() takes an ASCII code and returns the corresponding character. The code for a line break is 10, for a carriage return it is 13.

So we can express the following MySQL query for example:

UPDATE tab SET col = REPLACE(col, CHAR(13, 10), '');

With this, we replace all line breaks of the type \r\n with nothing - that is, we delete the line breaks. Other types of line breaks can of course be replaced in the same way by using the corresponding character codes.

Instead of writing CHAR(13,10) we can also write CHAR(13) + CHAR(10), which means the same. Using the plus we create a string consisting of the character with code 13 and the character with code 10. Because CHAR() can take multiple parameters, with CHAR(13,10) we get the same result.