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

Question

I didn't see any similar questions asked on this topic, and I had to research this for something I'm working on right now. Thought I would post the answer for it in case anyone else had the same question.

Answer 1

Using SQL statement, If you are trying to build a string then you can use CHAR() to insert the ASCII character for the line break into your string.

The following 3 supported characters which are given below, but CHAR(10) is for creating a new line.

Control character	Value
Line feed	char(10)
Carriage return	char(13)
Tab	char(9)

 For example, if you want to use a word mail merge to list all your cats then you can use the following

Example: 

If you want to use the word mail merge to list all vehicles you would use the following:

SELECT CHAR(10) + vehicles FROM T_Vehicles

STUFF and FOR XML PATH you can use inside a subquery.

For example, if you want to list all the vehicle that a person owns then the code should be written like this:

Query:

SELECT Person, STUFF

((SELECT char(10) + V.Vehicless FROM T_Vehicles V

     WHERE TP.Person = V.Person

    ORDER BY V.Vehicles ASC FOR XML PATH('')), 1, 1, '') AS Vehicles_Combined

FROM T_Persons TP

Output:

Person A

Vehicles

Car 1

Bike 1

Bicycle 1

Person B

Vehicles

Car 2

Bike 2

Bicycle 2

Learn all about SQL through this SQL Certification Course! 

Answer 2

Let’s say you want to insert a line break. You can use the newline char combinations like : CHAR(13)+CHAR(10). For example you can use below code for reference. 

DECLARE @MyString NVARCHAR(100);

SET @MyString = 'Hello' + CHAR(13) + CHAR(10) + 'World';

SELECT @MyString AS Result;