How To: Work with carriage return or line breaks in datasets

Relates to Microsoft SQL Server / Microsoft Excel

This guide has been written to assist users when converting data from Microsoft SQL Server Management Studio to data manipulation tools such as Microsoft Excel.

Overview:

Sometimes you may be required to copy the contents of the SQL Server Management Studio result set into an Excel spreadsheet. If the original source records in one of your tables have text with “line break” system characters, pasting to Excel will copy such characters.

Whilst this will typically present fine within Excel, third party applications may struggle to interpret the character if the data is imported/copied elsewhere, or if they do interpret the line break correctly, it may cause your application to misinterpret the data in the cell/input.

To avoid this, the following query can be used to truncate the Line Break/Carriage Return character:

SELECT REPLACE(<Column_Name>,CHAR(13)+CHAR(10),' ') AS [Column_Name]

• Note: Char(13) is used for identifying and removing Carriage Return, and Char(10) for removing Line Feed/break characters.

The output can then be copied to other applications without worry that Carriage Returns or Line Breaks will affect your data.

Want to learn more?

Click here to login to our Learning Management System

Click here to request access