SQL – How to insert new line/line break in string

If you’re building a string using an SQL statement, you can use CHAR() to insert the ASCII character for a line break into your string.

There are three supported characters but the one to create a new line is CHAR(10).

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

For example, if you were wanting to use a word mail merge to list all your cats you would use the following

SELECT CHAR(10) + cats FROM T_Cats

Stepping it up a gear you can use this inside a sub-query using STUFF and FOR XML PATH.

For example if you wanted to list all the cats a person owned

SELECT Person, STUFF
( (SELECT char(10) + C.Cats FROM T_Cats C
    WHERE TP.Person = C.Person
    ORDER BY C.Cats ASC FOR XML PATH('')), 1, 1, '') AS Cats_Combined
FROM T_Persons TP

This would return something like

Person 1 Cat 1
Cat 2
Cat 3
Person 2 Cat 4
Cat 5
Cat 6

Reference: http://stackoverflow.com/questions/31057/how-to-insert-a-line-break-in-a-sql-server-varchar-nvarchar-string