Advertisement
Advertisement


How to insert a line break in a SQL Server VARCHAR/NVARCHAR string


Question

I didn't see any similar questions asked on this topic, and I had to research this for something I'm working on right now. Thought I would post the answer for it in case anyone else had the same question.

2013/07/10
1
575
7/10/2013 7:18:56 PM

Accepted Answer

I found the answer here: http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/

You just concatenate the string and insert a CHAR(13) where you want your line break.

Example:

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
SELECT @text

This prints out the following:

This is line 1.
This is line 2.

2012/04/03
291
4/3/2012 11:25:21 PM


Another way to do this is as such:

INSERT CRLF SELECT 'fox 
jumped'

That is, simply inserting a line break in your query while writing it will add the like break to the database. This works in SQL server Management studio and Query Analyzer. I believe this will also work in C# if you use the @ sign on strings.

string str = @"INSERT CRLF SELECT 'fox 
    jumped'"
2016/06/22

Run this in SSMS, it shows how line breaks in the SQL itself become part of string values that span lines :

PRINT 'Line 1
Line 2
Line 3'
PRINT ''

PRINT 'How long is a blank line feed?'
PRINT LEN('
')
PRINT ''

PRINT 'What are the ASCII values?'
PRINT ASCII(SUBSTRING('
',1,1))
PRINT ASCII(SUBSTRING('
',2,1))

Result :
Line 1
Line 2
Line 3

How long is a blank line feed?
2

What are the ASCII values?
13
10

Or if you'd rather specify your string on one line (almost!) you could employ REPLACE() like this (optionally use CHAR(13)+CHAR(10) as the replacement) :

PRINT REPLACE('Line 1`Line 2`Line 3','`','
')
2015/09/17

Following a Google...

Taking the code from the website:

CREATE TABLE CRLF
    (
        col1 VARCHAR(1000)
    )

INSERT CRLF SELECT 'The quick [email protected]'
INSERT CRLF SELECT 'fox @jumped'
INSERT CRLF SELECT '@over the '
INSERT CRLF SELECT '[email protected]'

SELECT col1 FROM CRLF

Returns:

col1
-----------------
The quick [email protected]
fox @jumped
@over the
[email protected]

(4 row(s) affected)


UPDATE CRLF
SET col1 = REPLACE(col1, '@', CHAR(13))

Looks like it can be done by replacing a placeholder with CHAR(13)

Good question, never done it myself :)

2008/08/27

I got here because I was concerned that cr-lfs that I specified in C# strings were not being shown in SQl Server Management Studio query responses.

It turns out, they are there, but are not being displayed.

To "see" the cr-lfs, use the print statement like:

declare @tmp varchar(500)    
select @tmp = msgbody from emailssentlog where id=6769;
print @tmp
2014/12/24

All of these options work depending on your situation, but you may not see any of them work if you're using SSMS (as mentioned in some comments SSMS hides CR/LFs)

So rather than driving yourself round the bend, Check this setting in

Tools | Options

which will replace the

2019/12/05

Source: https://stackoverflow.com/questions/31057
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]