SQL SERVER – Difference between Line Feed (\n) and Carriage Return (\r) – T-SQL New Line Char

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?

Solarwinds

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

SQL SERVER - Difference between Line Feed (\n) and Carriage Return (\r) - T-SQL New Line Char lf1

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

SQL SERVER - Difference between Line Feed (\n) and Carriage Return (\r) - T-SQL New Line Char lf2

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)

Solarwinds
,
Previous Post
SQL SERVER – 2008 – Policy-Based Management – Create, Evaluate and Fix Policies
Next Post
SQLAuthority News – MVP Award Renewed

Related Posts

75 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.
    :)

    Reply
  • 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?

    Reply
    • Are you using char(10)+char(13) for @NewLineChar?

      Reply
      • 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

    Reply
  • 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?

    Reply
  • Kees C. Bakker
    March 15, 2011 5:00 pm

    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!

    Reply
  • Very good sample……..

    Reply
  • 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

    Reply
  • 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

    Reply
  • I am generating SQL script through Java. \r\n does not work, what should I be using in Java?

    Reply
  • 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

    Reply
  • 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 :)

    Reply
  • 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:

    Reply
  • beautiful explanation .. thanks

    Reply
  • Anirudha Gohokar (@AnirudhaGohokar)
    June 25, 2012 6:02 pm

    /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

    Reply
  • 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

    Reply
  • 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.

    Reply
  • Thanks!

    Reply
  • Great!!! I was in a confusion for a long long time… got it cleared now..
    thanks

    Reply
  • 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

    Reply
  • thanks for such nice explanation:-)

    Reply

Leave a Reply

Menu