How to insert a line break in a SQL Server VARCHAR/NVARCHAR string
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.
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.
Popular Answer
char(13) is CR. For DOS-/Windows-style CRLF linebreaks, you want char(13)+char(10), like:
'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'
Read more… Read less…
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'"
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','`','
')
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 :)
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
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
