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
January 23, 2008 at 12:24 pm |
Hi Mark,
Interesting post I enjoyed reading it. I have previously written something similar, you have put this concept very well.
http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/
Regards,
Pinal Dave (http://www.SQLAuthority.com)
November 14, 2008 at 12:16 pm |
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
December 30, 2008 at 8:58 am |
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)