How to insert a line break in a SQL Server VARCHAR/NVARCHAR string

Meet the ASCII family

In ASCII, our beloved characters CHAR(13) and CHAR(10) signify Carriage Return (CR) and Line Feed (LF), respectively. These guys are your ticket to a more readable SQL VARCHAR.

Let's break some lines

Create a multi-line string by placing a carriage return and line feed where you want a break:

DECLARE @Letter NVARCHAR(MAX) = 'Dear Customer,' + CHAR(13) + CHAR(10) + 'Your order is confirmed.' + CHAR(13) + CHAR(10) + 'Thank you.';
SELECT @Letter; -- A very formal and properly formatted letter


Forget monotonous strings, your SQL text is now a piece of art.

The big reveal

New line breaks don't like to brag. They are hidden in SSMS default settings. But with PRINT, you can make them the center of attention:

PRINT @Letter; -- Your letter is so polite it just printed itself


Stored line breaks

Your line breaks can shine in SQL Server Stored Procedures. They make complex queries feel like breeze:

CREATE PROCEDURE WriteLetter
AS
BEGIN
    DECLARE @Letter NVARCHAR(MAX) = '...' -- as above
    PRINT @Letter; -- Your stored procedure is now a smooth operation. Break a line, not a sweat!
END;


"The train has left the station!" - space issues

Using line breaks mean you're adding two more characters to the party. So, while it makes your SQL text look good, it might increase your storage. Check the effect with LEN().

SELECT LEN(@Letter); -- Length check: Because every character counts! Literally.


Hands-on applications

Making your report cards.

Line breaks come in handy for enhancing SQL Server reports. Nothing like a well-formatted report to speed things up.

Welcome to the dynamic world

When building strings dynamically, STRING_AGG() and CONCAT_WS() can be your partners-in-crime for including line breaks:

SELECT STRING_AGG(ColumnName, CHAR(13) + CHAR(10)) FROM MyTable; -- New lines, no problem


Migrating in style

Whether it's importing data into SQL Server or sending data on a vacation (exports), maintaining line breaks is crucial.

Proceed with caution

Line breaks, once popular, are now shunned by front-end applications, making them problematic. Be sure to check compatibility, or risk a meltdown.