New Line in SQL Server Strings: CHAR(13) and CHAR(10)

A new line in SQL Server is a character inside the string, not formatting. I use line breaks when a message or generated text needs to be easier to read. I tested each example below on SQL Server 2025.

Carriage Return and Line Feed

CHAR(13) represents a carriage return, and CHAR(10) represents a line feed. A common Windows line ending combines them in that order. The application that displays the text decides how to draw each character.

DECLARE @message varchar(100) = 'First line'
    + CHAR(13) + CHAR(10) + 'Second line';
PRINT @message;

In the sqlcmd test, First line and Second line appeared on separate lines. Check your receiving tool too. Both characters are part of the string. PRINT does not add them between those words for us.

Check the Stored Characters

You can inspect the character codes without relying on how the results grid draws the value. The two control characters follow the ten characters of First line, so they sit at positions 11 and 12.

DECLARE @message varchar(100) = 'First line'
    + CHAR(13) + CHAR(10) + 'Second line';
SELECT ASCII(SUBSTRING(@message, 11, 1)) AS CR_Code,
       ASCII(SUBSTRING(@message, 12, 1)) AS LF_Code,
       DATALENGTH(@message) AS StoredBytes;

The query returns 13, 10 and 23 bytes. DATALENGTH reports bytes, so its result differs for other data types and encodings.

Find Rows That Contain Line Breaks

Imported text can carry line breaks nobody planned. I built four notes. One has CRLF, one has no break, one has only a line feed, and one has only a carriage return. Two LIKE tests tell the three kinds apart.

CREATE TABLE dbo.Notes (NoteId int PRIMARY KEY, NoteText varchar(100) NOT NULL);
INSERT dbo.Notes (NoteId, NoteText) VALUES
 (1, 'Call back at 5' + CHAR(13) + CHAR(10) + 'Ask about rice'),
 (2, 'Single line note'),
 (3, 'Unix style' + CHAR(10) + 'line break'),
 (4, 'Old style' + CHAR(13) + 'line break');
GO
SELECT NoteId,
       CASE WHEN NoteText LIKE '%' + CHAR(13) + '%' THEN 1 ELSE 0 END AS HasCR,
       CASE WHEN NoteText LIKE '%' + CHAR(10) + '%' THEN 1 ELSE 0 END AS HasLF
FROM dbo.Notes ORDER BY NoteId;

Note 1 has both characters. Note 2 has neither. Note 3 has only the line feed, and note 4 has only the carriage return. A search for CHAR(13) alone would miss note 3. A search for CHAR(10) alone would miss note 4.

Remove Line Breaks From Data

To flatten a note to one line, replace the characters with a space. Replace the pair first. If you replace each character on its own, one CRLF becomes two spaces. In my test, note 1 showed two spaces where the break was in the first column. The second column showed one space.

SELECT NoteId,
       REPLACE(REPLACE(NoteText, CHAR(13), ' '), CHAR(10), ' ') AS TwoSpaces,
       REPLACE(REPLACE(REPLACE(NoteText, CHAR(13) + CHAR(10), ' '), CHAR(13), ' '), CHAR(10), ' ') AS OneSpace
FROM dbo.Notes ORDER BY NoteId;

Why RTRIM Leaves a Trailing Break

RTRIM removes trailing spaces, not line breaks. A string that ends in CRLF kept all 6 bytes after RTRIM. TRIM with a list of characters removed the pair and left 4 bytes. That form needs SQL Server 2022 or later.

DECLARE @t varchar(50) = 'Text' + CHAR(13) + CHAR(10);
SELECT DATALENGTH(@t) AS Original,
       DATALENGTH(RTRIM(@t)) AS AfterRtrim,
       DATALENGTH(TRIM(CHAR(13) + CHAR(10) FROM @t)) AS AfterTrimCharacters,
       LEN(@t) AS LenWithBreak,
       LEN('Text   ') AS LenWithSpaces;

LEN shows the same trap from the other side. It ignores trailing spaces, so ‘Text’ plus three spaces has length 4. It counts the line break, so ‘Text’ plus CRLF has length 6.

Build Multi-Line Text

CONCAT_WS joins values with a separator, and STRING_AGG joins rows. Both accept a line break as the separator. That is a clean way to build a message from several parts. CHAR(9) is a tab, and the same function builds tab-separated text. The third query returned one line with three values.

SELECT CONCAT_WS(CHAR(13) + CHAR(10), 'Line one', 'Line two', 'Line three') AS Joined;
SELECT STRING_AGG(NoteText, CHAR(10)) WITHIN GROUP (ORDER BY NoteId) AS AllNotes
FROM dbo.Notes WHERE NoteId IN (2, 3);
SELECT CONCAT_WS(CHAR(9), 'Tea', 'Assam', '4.50') AS TabSeparated;

A Line Break Ends a Single-Line Comment

A line break can make generated SQL easier to read. It can also change its meaning. A -- comment runs to the end of the line. When I joined two pieces of dynamic SQL with no break, the comment swallowed the second column. With a line feed between them, both columns came back.

DECLARE @sql nvarchar(200) = N'SELECT 1 AS a -- first column';
SET @sql += N', 2 AS b;';
EXEC sys.sp_executesql @sql;
SET @sql = N'SELECT 1 AS a -- first column' + NCHAR(10) + N', 2 AS b;';
EXEC sys.sp_executesql @sql;

The first call returned only column a. The second returned a and b. GO is a batch separator for tools such as SSMS. SQL Server does not execute it inside dynamic SQL.

Check the Tool That Receives the Text

Results to Grid, Results to Text and other applications can draw line breaks differently. A single carriage return or line feed can look different from a CRLF pair. Test the output in the application that will use it. If you export a value with a line break to CSV, the export must quote the field. A newline inside a quoted field is not a new record.

A line break is not formatting on a screen, it is data that the receiving tool must interpret.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.