Joomla Jumpstart

Finding newline characters can be very useful when data may contain these unwanted characters (especially if the data will be sent into another MySQL statement). Fortunately, searching for them in MySQL is fairly easy. You just need to know what kind of newline characters you're looking to find.

Standard / Linux newline characters

To find all of the rows that have newlines, you can simply use the backslash (\) escape character like this:

SELECT * FROM articles WHERE content like '%\n%'; 

DOS / Windows newline characters

If your text is stored with DOS/Windows newline characters, use:

SELECT * FROM articles WHERE content like '%\r\n%';

These queries can be extremely useful when performing data cleaning on a MySQL table.

Additional sources

• Wikipedia information on newlines
• MySQL String comparison functions
• MySQL Regular expression with special characters