SQL: Insert a linebreak in varchar string

Question

I've searched StackOverflow for all the possible solutions concerning how to insert a linebreak in a SQL text string. I've referred this link but to no avail. How to insert a line break in a SQL Server VARCHAR/NVARCHAR string

But none of the solutions are working for me.

This is what I'm trying to do:

insert into sample (dex, col) 
values (2, 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.')

But this is the output generated: (Select Col from sample where dex = 2)

This is line 1. This is line 2.

This is the output that I desire:

This is line 1.
This is line 2.

I'm using SQL server and SSMS if that helps.

Any ideas why it isn't working?

Answer 1

Well your query works perfectly fine. SSMS by default shows all query out put in the grid view, which does not display the line break character.

To see it you can switch to text view using cntrl + T shortcut or like below

The results I got for your query are below( and they work)

Answer 2

It works perfectly:

CREATE TABLE sample(dex INT, col VARCHAR(100));

INSERT INTO sample(dex, col) 
VALUES (2, 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.');

SELECT *
FROM sample;

LiveDemo

Output:

The "problem" is SSMS grid view that skips newline characters (and others too). Otherwise you will get different rows height like in Excel.

---

You could observe the same behaviour in SEDE.

LiveDemo-SEDELiveDemo-SEDE-TextView

Output:

---

You could compare it using:

SELECT 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.';
PRINT  'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.';