This turned out to be a dumb question – or rather it had a simple answer. I wanted to insert a carriage return and line feed into a sql statement I was generating in T-SQL so that it was more readable. My first question was which comes first, the chicken or the egg? Just kidding – actually the question was, which comes first char(13) or char(10)? Turns out, seems like char(13) comes first. I’ve always been inside a programming language so you could use things like Environment.NewLine – so I hadn’t ever memorized it. First question answered.
The second question was about actually getting the carriage return to show up in the text. Turns out that you can’t put (or I couldn’t figure out a way) a carriage return into a statment that is SELECTed. Rather you need to use the PRINT statment and then you’ll be good to go.
I never found anyone that really stated that you can’t do it in a SELECT and to do it in a PRINT. So – I thought I’d just get it written down here – if not for my reference later!
Have a good day!
Mark
Cloudy With a Chance of Data 
Hi Mark,
Interesting post I enjoyed reading it. I have previously written something similar, you have put this concept very well.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
Funny. Drumming up some more web hits Dave?
Sure enough Mark, I tried:
SELECT ‘this’ + CHAR(10) + ‘&’ + CHAR(13) + ‘that’
…and it comes out “this & that’. When I try this:
PRINT ‘this’ + CHAR(10) + ‘&’ + CHAR(13) + ‘that’
…it comes out like:
this
&
that
…how strange. I’m in a pickle as to how to get a carriage return into a varchar that I pass to Database Mail in SQL 2005. I’ll keep plugging away at it…
–Joe
This is normal, in order to see the correct output make sure to select output to text, NOT output to grid (the grid doesn’t handle new lines)
— Try the below
— this blog changes single quotes into curlies. Change back before running.
declare @v varchar(max)
select @v = ‘this’ + CHAR(10) + ‘&’ + CHAR(13) + ‘that’
select @v
print @v
— note, the line breaks here are intentional.
select @v = ‘this
&
that’
select @v
print @v
— The carriage returns are there.
Hi,
I’m actually trying to code a SQL statement that will insert text containing a carriage return and line feed into a nvarchar(max) record, but for some reason the CHAR(10)+CHAR(13) don’t seem to do anything. Does anyone know why? here’s my statement
update loc_category set categoryname=@categoryname, categoryhistory= (select categoryhistory from loc_category where categoryid = @categoryid)+char(10)+char(13)+convert(varchar, getDate(),101)+’ Updated record’ where categoryid = @categoryid
Everything works except there is no new line before the date entry.
Thanks
John.
I’m actually having a similar problem as posted on March 21st. Did anyone ever come up with a solution?
I’m writing an update statement to merge 2 columns. I want to add a hard return in front of the 2nd column to improve readability in the gui for the users. This was the statement with the char(13):
update defects
set description = ISNULL(CAST(description AS VARCHAR(max)),’ ‘) + CHAR(13) + ISNULL(cast(replicationprocedures AS varchar(MAX)),’ ‘)
Unfortunately that doesn’t work, it just sticks the first character from replicationprocedures after the last character from defects.
Any help would be greatly appreciated 🙂
Thanks,
David
Hi David,
I think in my situation the issue was how I was trying to display the record. I was trying to display it in a label I think and that’s why it never showed the CR. When I changed it to a multi line text field it worked if I recall right. I actually found a different way of doing what I needed and didn’t need this afterall. Hope this helps.
John.
@commentors,
char(13) — only Carriage Return (CR)
char(10) — only Line Feed (LF)
line break = CR+LF (in that order)
SQL Server shows a line break in output even if the text has only CR or only LF, but this is not technically proper. So make sure you use char(13)+char(10), atleast for Windows machines.
For other machines, see
Reference: http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/a87b9fbb-40dc-4a2e-83df-eab150620122/
this is a little different, but helpful if you want the text in a field to have carriage returns…
insert into TH_TEMPLATE_MAP values (‘182′,’Dear Customer,’||chr(13)||chr(10)||chr(13)||chr(10)||’Your internet service has reached….’);
the ‘||chr(13)||chr(10)||chr(13)||chr(10)||’ will cause the text to appear this way when it is shown on a screen…
Dear Customer,
Your internet service has reached…
When inserting data into the field, use a markup to indicate the line breaks.
When selecting the data use the ‘replace’ function to replace the with char(13).
eg.
Using the AdventureWorks DB, I modified the value of one of the fields in the Production.ProductDescription table
field value: Aluminum alloy cups; Large diameter spindle.
sql statement:
Select Replace((Select [Description] from Production.ProductDescription where ProductDescriptionID=4), ”, Char(13))
Output:
Aluminum alloy cups;
large diameter spindle.
(1 row(s) affected)
Send the email as HTML and embed
At the @body argument, have ‘ {….. remainder of body test}’
In my case, I had data lines, so i had a select statement:
SELECT ”” + [mydata] FROM [mytable]