Today, we will examine something very simple and very generic that can apply to hordes of programming languages. Let’s take a common question that is frequently discussed – What is difference between Line Feed (\n) and Carriage Return (\r)?
Prior to continuing with this article let us first look into few synonyms for LF and CR.
Line Feed – LF – \n – 0×0a – 10 (decimal)
Carriage Return – CR – \r – 0×0D – 13 (decimal)
Now that we have understood that we have two different options to get new line, the question that arises is – why is it so?
The reason is simple. Different operating systems have a different way of understanding new line. Mac only understands ‘\r’ as new line, while Unix and Linux understand ‘\n’ as new line character. Our favorite OS windows needs both the characters together to interpret as new line, which is ‘\r\n’. This is the reason why a file created in one OS does not open properly in another OS and makes it messy.
Now, let us see how we can create a new line in SQL Server. It is a very simple script yet very useful when we have to do run print something or generate scripts. I have illustrated two examples below that are very easy to understand. In the first example, there are no new line chars inserted and for the same, everything is displayed in a single line. However, in the second example, new line char is inserted and the lines are separated with a new line.
Example 1: No new line feed char
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL SELECT SecondLine AS SL' )
GO
Example 2: With new line feed char
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL ' +@NewLineChar + 'SELECT SecondLine AS SL' )
GO
I hope my examples make things more clear to you all. Let me have your feedback on this article.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
This is really cool information. I never think this minute difference in my experience.
Nice tip…
Thank you…
In my system only one of them is producing the new line. I do not need bothe chars togather.
Both of below statements insert line char
PRINT (‘SELECT FirstLine AS FL ‘ + char(13) + ‘SELECT SecondLine AS SL’)
PRINT (‘SELECT FirstLine AS FL ‘ + char(10) + ‘SELECT SecondLine AS SL’ )
Hi
I can see a space in front of the second line in the output this is happening by use of newline char as a variable, infact if i use char(13) or char(10) it isn’t producing space in front.
here is the code snippet
DECLARE @NewLineChar AS CHAR(2)
set @NewLineChar= CHAR(13)
PRINT (‘SELECT FirstLine AS FL ‘ + @NewLineChar + ‘SELECT SecondLine AS SL’ )
GO
Print ‘————————’
PRINT (‘SELECT FirstLine AS FL ‘ + char(13) + ‘SELECT SecondLine AS SL’)
Print ‘————————’
PRINT (‘SELECT FirstLine AS FL ‘ + char(10) + ‘SELECT SecondLine AS SL’ )
Is is some thing in the code which cause of space in the output?
Sanjay
Nice tip….
Thanks for the info.
Sanjay, the reason you are getting a space at the beginning of the second is due to the declaration of @NewLineChar as a fixed length 2 character string.
Some background on ‘Carriage Return’ and ‘Line Feed”.
Back in the ‘old’ days when I was first programming we used printers that had a fixed number of characters per line and fixed number of lines per page. Each character had a specific page location. “Carriage Return” brought the carriage back to the beginning of the current line. You could then overprint for bold or underline effects. “Line feed” moved the print head down 1 line, but kept the same horizontal position. So both were needed to move the print head to the beginning of the next line.
Just a clarification for anyone trying out this useful example.
The syntax :
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
is for SQL 2008 only (i.e. initializing variables inline as part of the variable declaration statement instead of using separate DECLARE and SET statements).
For SQL 2005, we’d need to use separate DECLARE and SET statements :
DECLARE @NewLineChar AS CHAR(2)
SET @NewLineChar= CHAR(13) + CHAR(10)