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 w3wiki. 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 w3wiki. This is SplitString'
SELECT @myString as myString
Output:
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:
- This is article.
- This is on w3wiki.
- This is SplitString
Query:
DECLARE @myString AS VARCHAR(100)
SET @myString = 'This is article.' + CHAR(10) + 'This is on w3wiki.' + CHAR(10) + 'This is SplitString'
SELECT @myString as myString
Output:
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:
- This is article.
- It is on w3wiki.
- 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 w3wiki.' + CHAR(13) + 'This is SplitString'
SELECT @myString as myString
Output:
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.
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.