CODEWITHSUNDEEP
sqlsql-servert-sql
90abyss
90abyss

Reputation: 7347

SQL: Insert a linebreak in varchar string

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?

Upvotes: 15

Views: 89637

Answers (4)

user2092071
user2092071

Reputation: 61

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.

Upvotes: 6

Thomas G
Thomas G

Reputation: 10216

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

Upvotes: 6

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

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.';

Upvotes: 11

DhruvJoshi
DhruvJoshi

Reputation: 17126

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

Upvotes: 14

Related Questions