How to Insert Line Break in SQL Server String?

In SQL Server there are various datatypes like int, float, char, nchar, etc but especially while we are dealing with text in VARCHAR and NVARCHAR columns, we might run into situations where we need to make the text look cleaner by adding line breaks. This could be for better organization, and readability, data for display, or working with flat files, excel, or other applications.

In this article, we will break down simple methods to easily insert line breaks into our VARCHAR and NVARCHAR strings in SQL Server. Whether we are new to SQL or have some experience, these techniques will help us to improve the way to look at our data and make it more user-friendly.

Introduction to Insert Line Break in SQL Server String

We can solve our problem using CHAR (integer_expression). It is used to insert control characters into the string. CHAR(10) and CHAR(13) are used to insert Line Feed(LF) and Carriage Return(CR) respectively.

Before going to implementation and example we will understand what is Line Feed and Carriage Return.

• Line Feed: It means moving one line forward. Its code representation is \n.
• Carriage Return: It means moving the cursor to the beginning of the line. Its code representation is \r.

In this article, we are going to see the use of both.

Syntax:

SET @var = @string1 + CHAR(10) + @string2
OR
SET @var = @string1 + CHAR(13) + @string2

So, let’s define a varchar string and split it into multiple parts.

Examples of How to Insert Line Break in SQL Server String

Example 1: Let’s Declares a Variable @myString of Type VARCHAR(100) and Sets Some value to it

Let’s Create a SQL script that declares a variable @myString of type VARCHAR(100) and sets its value to ‘This is article. It is on GeeksForGeeks. This is SplitString’. Finally, select and display the value of @myString as ‘myString‘.

Query:

DECLARE @myString as VARCHAR(100)
SET @myString = 'This is article. It is on GeeksForGeeks. This is SplitString'
SELECT @myString as myString

Output:

This is the output of the SQL. We can see the output in the single line

Explanation: We have successfully declared a variable @myString of VARCHAR(100) and set strings.

Example 2: Let’s Store Multi-Sentence String with Line Breaks Within a SQL Server

Let’s add CHAR(10) and see how it works.

Suppose we need to temporarily store a multi-sentence string with line breaks within a SQL Server query. This string contains three sentences which are:

1. This is article.
2. This is on GeeksForGeeks.
3. This is SplitString

Query:

DECLARE @myString AS VARCHAR(100)
SET @myString = 'This is article.' + CHAR(10) + 'This is on GeeksForGeeks.' + CHAR(10) + 'This is SplitString'
SELECT @myString as myString

Output:

Tada! We have broken down our string into 4 substrings. SQL Server treats it as \n character or line break

Explanation: In the above example, in results, we can see that our string variable @myString is broken into multiple lines. So by using CHAR(10), line break is added into the varchar/nvarchar string. Please keep one thing mind that integer “10” means Line Feed in ASCII. More line breaks we need, more CHAR(10) can be added into the string.

Example 3: Let’s Store a Multi-Line String

Let Suppose we want to store a multi-line string that appears as:

1. This is article.
2. It is on GeeksForGeeks.
3. This is SplitString

in a SQL Server variable named @myString and then retrieve it.

Query:

DECLARE @myString AS VARCHAR(100)
SET @myString = 'This is article.' + CHAR(13) + 'It is on GeeksForGeeks.' + CHAR(13) + 'This is SplitString'
SELECT @myString as myString

Output:

Output is same as above example but /r code is used behind the scene by SQL Server

Explanation: In this example, we can that line break is added and there are multi strings. So by using CHAR(13), line break is added into the varchar/nvarchar string. Please keep one thing mind that integer “13” means Carriage return in ASCII.

Conclusion

Throughout this article, we have understand the methods for breaking or splitting lines using CHAR(10) and CHAR(13). To precisely introduce a line break in the string, we have utilized CHAR(10), which functions as the equivalent of \n for SQL Server. After reading whole article now you have good understanding about how to insert line breaks in SQL Server through various implementations and examples.