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 – 0x0a – 10 (decimal)
Carriage Return – CR – \r – 0x0D – 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 (https://blog.sqlauthority.com)
76 Comments. Leave new
Thank you Dave very clear and concise. Very good to know.
Any Ideas why is this not working: My query is:
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10); (‘USE tempdb;’ +@NewLineChar + ‘GO’ +@NewLineChar +’CREATE VIEW temp_view AS select name from sys.databases’)
It gives me error message:
Msg 102, Level 15, State 1.
Incorrect syntax near ‘USE tempdb;’. (Line 1)
What is the problem here? Thanks
That was very helpful. No more cramming now that I know why. Thanks Pinal
It is very helpful for me sir Thank you
Can u Tell me how can I Get This in Grid result set
Try this:
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10);
print (‘USE tempdb;’ +@NewLineChar +’go’ +@NewLineChar+’CREATE VIEW temp_view AS select name from sys.databases’)
I think you are simply missing the spaces ahead and after the +, which screws up right interpretation of the code line with Port of Call, Vessel Name and Vessel Number.
i have to extract line 2 from a varchar max notes column. what is teh code for this?
when i tries to import data from excel to SQL server 2012, new rows are creating for the cells that has line feeds/carriage returns and vice versa.
Please help me to sort out this issue
What I do not understand is why T-SQL or SSMS is handling CHAR(10)+CHAR(13) not the same as CHAR(13)+CHAR(10). Shouldn’t it be the same logically?
It was so helpful but still i have one query what is AS CHAR(2)?
It’s the variable declaration ..
CHAR(10) alone used to work for me in SSMS queries and with latest SSMS both CHAR(10) or CHAR(13) and CHAR(10)+CHAR(13) does not work. Not sure what changed.
Thanks, that really helped me out!
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
SELECT ‘SELECT FirstLine AS FL ‘ + @NewLineChar + ‘UNION ALL’+ @NewLineChar +’SELECT’
not working