15

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?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
90abyss
  • 7,037
  • 19
  • 63
  • 94
  • If you're running it from within SQL Management Studio, it won't display that way, probably, because of the limitations on the way the data is presented in the system. If you copy and paste it to somewhere else, it will be on two lines. – phroureo Apr 13 '16 at 19:55
  • len(col) will have the CR LF – paparazzo Apr 13 '16 at 20:28

4 Answers4

14

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

enter image description here

The results I got for your query are below( and they work) enter image description here

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
11

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:

enter image description here

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:

enter image description here

enter image description here

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.';
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
6

The CR/LF chars are there, it's just that in the format of your output, they are being ignored.

I've created a fiddle to illustrate this, with 2 VARCHAR columns. In the first one I insert the text with no CR/LF, in the second I include them

CREATE TABLE sample (dex INT, colnocr VARCHAR(50), col VARCHAR(50)) ;
insert into sample (dex, colnocr, col) values 
(2, 
 'This is line 1.' + 'This is line 2.',
 'This is line 1.' + CHAR(13) + CHAR(10) + 'This is line 2.'
)
;

if you run the query

SELECT * FROM sample

The result in plain text are:

| dex |                        colnocr |                              col |
|-----|--------------------------------|----------------------------------|
|   2 | This is line 1.This is line 2. | This is line 1.
This is line 2. |

but if you run it in tabular :

dex     colnocr                                                     col
2       This is line 1.This is line 2.      This is line 1. This is line 2.

Check it : SqlFiddleDemo

Thomas G
  • 9,886
  • 7
  • 28
  • 41
6

A bit late to this discussion, but in SSMS 2016, there is an option on the Tools | Options menu under Query Results / SQL Server / Results to Grid called "Retain CR/LF on copy or save". Checking this box will allow you to copy values from a cell in a grid result to, say, another query window and still have the line breaks.

user2092071
  • 61
  • 1
  • 3