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
Hi Dave,
I am a regular reader of ur blog….Never asked any questions but whenever i had any query i just came here and got the answer…like this one…..Thanks.
:)
Dave,
I like the idea of declaring a variable for the new line and have cleaned up some of my sql. I am currently using sendmail on sql 2008 to obviously send email. However I’m having some trouble formatting the body of the email.
here is an example of the sql…
‘Account Name: ‘ + @Customer_Name + @NewLineChar +
@NewLineChar +
‘Port of Call: ‘+ @xFBB_PortofCall +’ ‘+ @NewLineChar +
‘Vessel Name: ‘ + @xFBB_Vessel_Name +’ ‘+ @NewLineChar +
‘Vessel Number-IMO: ‘ + @xFBB_Vessel_Number +’ ‘+ @NewLineChar +
‘ETA Date: ‘ + @xFBB_ETA_Date + ‘ ‘ + ‘ETA Local Time: ‘ + @xFBB_ETA_Time + @NewLineChar +
Here is what the email looks like:
Account Name: H Fluggesaft
Port of Call: Port of Rotterdam – Pier 33 Vessel Name: The Giant Maria Callendar Vessel Number-IMO: VS23409
ETA Date: 12/16/2010 ETA Local Time: 08:00 PM
The problem is on the line with Port of Call: ideally I want a CRLF after ‘Pier 33’ and before ‘Vessel Name:’ however it seems to completely disregard the @NewLineChar function unless I insert a 2nd one in whcih case I then get an extra space between each row. Seems to work fine for Account Name and on the ETA lines just not in between. All variables are declared varchar any ideas?
Are you using char(10)+char(13) for @NewLineChar?
Yes I’ve declared the variable as:
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
It works fine every where else just not on those 3 lines (i.e.):
Port of Call:
Vessel Name:
Vessle Number-IMO:
I thought adding extra space between the field and the variable might force it to crlf (+’ ‘+) but that isn’t working either, I’ve removed the extra space but still get the same results:
@NewLineChar +
‘Port of Call: ‘+ @xFBB_PortofCall + @NewLineChar +
‘Vessel Name: ‘ + @xFBB_Vessel_Name + @NewLineChar +
‘Vessel Number-IMO: ‘ + @xFBB_Vessel_Number + @NewLineChar +
‘ETA Date: ‘ + @xFBB_ETA_Date + ‘ ‘ + ‘ETA Local Time: ‘ + @xFBB_ETA_Time + @NewLineChar +
FYI, there are more lines above and below and all those lines work properly with the @NewLineChar variable this one just run’s on for some reason.
I am using T-SQL to update the online help in an application. The help is displayed when the mouse hovers over the field.
I do not have source code.
To make the help more readable, I have used CR / LF to create new paragraphs.
I do not like embedding control characters in fields, it can cause problems for people who come after me.
Is there a better or standard way to do this or must I use CR / LF
Hello Dave,
I’m running into a problem related to carriage return, line feeds in converting an application from SS 2005 to SS 2008 R2.
In the earlier version of the app, using SS 2005, I was able store multiple lines of text, each terminated with “CHAR(13)+CHAR(10)” into a text field in a SQL data record.
However I find that with SS 2008 R2, the field doesn’t seem to actually store the characters, but converts them to spaces?
This happens both with in my VB code and when I try it directly in the SS Management Studio. Doesn’t seem to matter if the target field is type TEXT (now deprecated) or VARCHAR(MAX).
Following is an example of what I mean:
INSERT INTO [adminSLOT].[dbo].[ProcessLogs]
([SessionID]
,[UserID]
,[LogText])
VALUES (
CONVERT(varchar(22),GETDATE())
,’Me’
,’This is one line of code.’ + CHAR(13) + CHAR(10) + ‘Here is another.’ + CHAR(13) + CHAR(10) + ‘Third and last line.’)
It works in 2005, but not in 2008R2?
Follow-up: Messing with the Results as Text option seems to have fixed this problem for me.
Thanks a lot Pinal! I just want to let you know that I enjoy your posts very much. They’re filled with information and good examples. Keep up the good work!
Very good sample……..
Nice post.
I have a question:
If want to do this:
print ‘bla bla’
WAITFOR DELAY ’00:00:10′;
Print ‘ After bla’
And I want something like this:
bla bla
on the screen and after 10 seconds
bla bla After bla
a print prints a CRLF right a way
— Sorry for my poor English and I borrow the words from Hennie de Nooijer
Hi,
Dave,
Could u please help me in this:
I have to convert multiple rows (Record-set as in Table1) returned from a query into a single column data(As in Table2) with line in between the row data and store it to another table.
Is this possible using CR / LF, if yes please let me know how to use the same.
Table1
RowNumber Data
1 A
2 B
3 C
4 D
5 E
Table2
RowNumber Data
1 A
B
C
D
E
Thanks in advance – Lakshmi
This is the formation issue which should be done in the front end application
cant this be handled using some logic?
Search for rowset concatenation in this site
I am generating SQL script through Java. \r\n does not work, what should I be using in Java?
Hi all ,
I am exporting data from SQL server 2005 to a text file using stored procedure. But line break between two records are not working. My code looks like this
SET @Data = @FNAME
SET @Data = @Data + CHAR(13) + @LName
I also tried \r\n , CHAR(13) + CHAR(10) etc.
Thanx
You need to set the result mode to Text in the Query Analyser
I know it’s an old post, but Apple only used \r to OS9 – OSX, a Unix, uses \n like the rest of the civilized world :)
For Oracle databases, one can make use of REPLACE function as follows –
REPLACE(Con.COMMENTS, CHR(13) || CHR(10), ‘ ‘) as “Updated_Comments”
For a detailed example, please refer to following link:
beautiful explanation .. thanks
/r—->says go to starting position of same line
/n—->says go to next line
so
/r/n—>goes to starting position of next line
Phew ,PinalDave,
This is the closest article to what im stuck with.
Hope you or anyone else can help me .How can we insert a cell in EXCEL which has Line Feed (n) and Carriage Return (r) into MSSQL ,then later display it in gridview as multiple rows ?
Below is my code .I have used SqlBulkCopy to import excel data to mssql.Which is working FINE.But the rows in the cell are inserted into db as 1 long string and when displayed back in RIDVIEW its also 1 long string ,its not being separated as 2 rows.
One of the EXCEL cell has this content
this is 1st line in cell1
this is 2nd line in cell1
When i use SqlBulkCopy to import excel data to mssql.The above is inserted as “this is 1st line in cell1this is 2nd line in cell1 ”
How can i retain the “Carriage Return/Line Feed” from the excel cell into DB and then display it in Griveview ? Please help.Ive been searching high and low.Thank you.
—————————————————————-
Dim conectionstring As String = “”
If strExt.ToLower() = “.xls” Then
conectionstring = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & Excel & “;Extended Properties=Excel 8.0”
ElseIf strExt.ToLower() = “.xlsx” Then
conectionstring = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & Excel & “;Extended Properties=Excel 12.0”
End If
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(conectionstring)
ExcelConnection.Open()
Dim expr As String = “SELECT * FROM [Sheet1$] where not U_Id is null”
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()
SQLconn.ConnectionString = ConnString
SQLconn.Open()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
bulkCopy.DestinationTableName = “SL_DataInfo_Temp”
bulkCopy.ColumnMappings.Add(“U_Id”, “di_id”)
bulkCopy.ColumnMappings.Add(“File_Ref”, “di_fileRef”)
bulkCopy.ColumnMappings.Add(“Date”, “di_date”)
bulkCopy.ColumnMappings.Add(“Status”, “di_status”)
objDR = objCmdSelect.ExecuteReader
If objDR.HasRows Then ”And objDR.FieldCount >= 13 Then
bulkCopy.WriteToServer(objDR)
ExcelConnection.Close()
SQLconn.Close()
End If
End Using
hi dave i have developed a transactional Query to generate a file but there is a problem
The last record in the file is missing a carriage return and line feed. This is causing me problems as it is not consistent with other records.
Thanks!
Great!!! I was in a confusion for a long long time… got it cleared now..
thanks
HI I have code as below and I want to print out each items in one row. for example: action in first row, rep_sysid in second row. How can I do that? Thank you.
set nocount on
declare
@action varchar(1),
@rep_cd varchar(5),
@rep_sysid int,
@name_eng varchar(30),
@name_fre varchar(30),
@nature_eng varchar(30),
@nature_fre varchar(30),
@sort_order smallint
————————————————-
–Actions
–‘I’ – Insert
–‘U’ – Update
–‘D’ – Delete
select
@action = ‘U’,–
@rep_cd = ‘1234’,
@name_eng = ‘ABCD’,
@name_fre = ‘EFGH’,
@nature_eng = ‘ijkl’,
@nature_fre = ‘mnop’,
@sort_order = 1
————————————————-
select
@rep_sysid = REP_SYSID
from
REP
where REP_CD = @rep_cd
exec CHANGE_REP
@action,
@rep_sysid,
@name_eng,
@name_fre,
@nature_eng,
@nature_fre,
@sort_order
thanks for such nice explanation:-)