In our last post, we looked at newlines in the context of strings and files.

In this post, we will look at how to deal with newlines in SQL.

In C# (and most languages), you can indicate a newline directly in your text by simply passing the escaped newline - \n.

The following code:

var text = "The quick\nbrown fox\nwent home";
Console.WriteLine(text);

Will print the following:

The quick
brown fox
went home

Suppose we need to do the same thing with the SQL Server.

DECLARE @Text NVARCHAR(50);

SET @Text ='The quick\nbrown fox\nwent home';

SELECT
    @Text;

If we run the above T-SQL, we get the following result:

The quick\nbrown fox\nwent home

SQL Server does not understand escaped characters.

It does, however, allow you to pass the actual characters.

We can rewrite the script as follows:

-- Declare a newline character (LineFeed)
DECLARE @NewLine CHAR(1) = CHAR(13);

DECLARE @Text NVARCHAR(50);

SET @Text = N'The quick' + @NewLine + N'brown fox' + @NewLine + N'went home';

SELECT
    @Text;

If we run this, we get the following:

The quick
brown fox
went home

SQL Server will also allow you to use the carriage return directly

-- Declare a newline character (carriage return)
DECLARE @NewLine CHAR(1) = CHAR(10);

DECLARE @Text NVARCHAR(50);

SET @Text = N'The quick' + @NewLine + N'brown fox' + @NewLine + N'went home';

SELECT
    @Text;

This means if you pass both a carriage return and a line feed \r\n -

-- Declare a newline character (carriage return & line feed)
DECLARE @NewLine CHAR(2) = CHAR(10) + CHAR(13);

DECLARE @Text NVARCHAR(50);

SET @Text = N'The quick' + @NewLine + N'brown fox' + @NewLine + N'went home';

SELECT
    @Text;

You will get two line breaks between each line.

The quick

brown fox

went home

Keep this in mind if such text needs to be written directly into a file!

TLDR

T-SQL allows you to specify newlines by directly embedding the characters in the text.

Happy hacking!