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

by

If you’re building a string using an SQL statement, you can split results with “new lines” using CHAR() to insert the ASCII line break character into your string.

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

SELECT CHAR(10) + cats FROM T_Cats

This would return something like

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

How to include more information …

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

BONUS Tip

There are three supported ASCI characters …

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

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