66 Votes

MySQL: Line breaks in MySQL

Tip by Stefan Trost | Last update on 2022-12-30 | Created on 2012-07-22

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.


About the Author

AvatarYou can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? - sttmedia.com/contact
Show Profile


Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.


Ask your own question or write your own article on askingbox.com. That’s how it’s done.