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?

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)

About these ads

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

    • Hello,

      I am sending email using sp_send_dbmail.
      My @msg variable is set as

      set @msg= @msg + char(10)
      set @msg= @msg + ‘By: ‘ + @ReportedBy + char(10)
      set @msg= @msg + ‘On: ‘ + cast(getdate() as varchar(50)) + char(10)
      set @msg= @msg + ‘With Ticket Number :’ + @TicketNumber + char(10)+ char(10)
      set @msg= @msg + ‘Of Department:’ + @Department + char(10)
      set @msg= @msg + ‘For Module:’ + @Module + char(10)
      set @msg= @msg + ‘of Error Type:’ + @ErrorType + char(10)
      set @msg= @msg + ‘Having Priority:’ + @Priority + char(10)
      set @msg= @msg + ‘With Short Text: ‘ + @TicketShortText +’.’+ char(10)+ char(10)
      set @msg= @msg + ‘Please do not respond to this mail, as it was automatically generated.’ + char(10)+ char(10)
      set @msg= @msg + ‘Best Wishes From’ + char(10)
      set @msg= @msg + ‘System Administrator’

      it is showing in sql as
      Hello,

      The Following Ticket has been changed.
      By: Sandeep Vaidya
      On: Jan 11 2011 3:49PM
      With Ticket Number :10004

      Of Department:Information Technology
      For Module:IT
      of Error Type:Customization
      Having Priority:Low
      With Short Text: Testing To All Changes.

      Please do not respond to this mail, as it was automatically generated.

      Best Wishes From
      System Administrator

      But in email as
      Hello, The Following Ticket has been changed. By: Sandeep Vaidya On: Jan 11 2011 3:43PM With Ticket Number :10004 Of Department:Information Technology For Module:IT of Error Type:Customization Having Priority:Low With Short Text: Testing To All Changes. Please do not respond to this mail, as it was automatically generated. Best Wishes From System Administrator

      Thanks in advance
      Please advise.

      Pankaj Phukane

      Like

  1. 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’ )

    Like

  2. 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

    Like

    • I’m sure this is old, but your extra space comes because your variable (@NewLineChar) has the type of CHAR(2).

      If the value of @NewLineChar is only one character in length, then it will fill the remaining missing characters with a space.

      Your code only assigns @NewLineChar one character (CHAR(13)). So the Print line prints the Carriage Return and then the extra space at the end of the variable.

      Like

  3. 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.

    Like

  4. 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)

    Like

  5. Hi Pinal\All,
    This is very useful information.
    However I have a question. I tried to implement the above use of NewLineChar.
    I do not undersand why there is space printed before the 2nd column values.

    Please let me know what I am missing out here.

    Code Snippet :
    CREATE TABLE TEMP_TEST
    (GID INT IDENTITY(1,1),
    NAME NVARCHAR(1000))
    GO
    INSERT INTO TEMP_TEST(NAME) VALUES(‘ABC’)
    INSERT INTO TEMP_TEST(NAME) VALUES(‘XYZ’)
    INSERT INTO TEMP_TEST(NAME) VALUES(‘PQR’)

    DECLARE @NewLineChar AS CHAR(2)
    SET @NewLineChar = CHAR(13) + CHAR(10)
    SELECT GID ,@NewLineChar, NAME
    FROM TEMP_TEST

    OUTPUT LOOKS LIKE THIS :
    1
    ABC
    2
    XYZ
    3
    PQR

    —————————————————————————
    Note :
    1. The behaviour is same for all columns except for the 1st one.
    2. The behaviour remains the same if the order of the columns is changed the select
    i.e if I query as
    SELECT NAME , @NewLineChar, GID
    FROM TEMP_TEST
    Then the output will have spaces before the GID values.

    Like

  6. Hi All,
    Just so that everyone is on the same page regarding my question about the additional spaces I asked above.

    1. The spaces are not visible above in the above cut\paste of my work.
    2. The spaces do appear when
    i) I set results to Text.
    ii) I set results to file.
    iii) I export results to a file.

    Example of result set is something like this:
    1
    [SPACES]ABC
    2
    [SPACES]XYZ
    3
    [SPACES]PQR

    Off course you can just do a find and replace and fix it, but I am curious about what the reason for those extra spaces to appear out there.

    Thanks to all.

    Like

    • Hi,

      I’m wondering what you mean by “set results to …”. For example, how you set results to file? Do you use some external program or script to do it?

      And are you sure those are space-characters? If you open up the file in hex-editor does it show space-char 0x20 or something else?

      Like

    • Hi, Try using the below modified code.

      CREATE TABLE TEMP_TEST
      (GID INT IDENTITY(1,1),
      NAME NVARCHAR(1000))
      GO
      INSERT INTO TEMP_TEST(NAME) VALUES(‘ABC’)
      INSERT INTO TEMP_TEST(NAME) VALUES(‘XYZ’)
      INSERT INTO TEMP_TEST(NAME) VALUES(‘PQR’)

      DECLARE @NewLineChar AS CHAR(2)
      SET @NewLineChar = CHAR(13) + CHAR(10)
      SELECT cast(GID as nvarchar(10))+@NewLineChar+NAME
      FROM TEMP_TEST

      —————
      Output:
      —————————————————————————————————————————————————————————————————————————————————————-
      1
      ABC
      2
      XYZ
      3
      PQR

      (3 row(s) affected)

      Like

  7. Hi Marko,
    I am not using any external program or script all my work is done using Management Studio 2005.

    About “set results to …”
    i) I set results to Text.–> Ctrl + T
    ii) I set results to file.–> Ctrl + Shift + F
    iii) I export results to a file.–> Using the Import\Export feature. On this one I used the column delimiter as verticalbar {|}
    and then did a find and replace on it, to get expected results.

    I am not sure if its space or tab. but there are white spaces before the 2nd column data is printed.

    and yes the spaces continue to exist and appear in other editors as well.

    Thanks for looking into this one.

    –Nilesh

    Like

    • Ah. Okay. Now I got it. I’m using Sql Server Management Studio 2008 and it’s maybe producing different kinds of results but try this:

      SELECT GID ,@NewLineChar as FOOBAR, NAME FROM TEMP_TEST

      Now you see header name for the middle column also. Otherwise it’s empty but you should see four minus-signs left of NAME column.

      I think results just goes little berzerk from that line-break. I think the three spaces you see at the beginning of the line extra line is delimeter and/or padding, which both are spaces by default.

      So output goes like:

      GID-field @NewLineChar
      three-spaces-for-delimeter NAME-field line-break-by-SSMS
      GID-field @NewLineChar
      three-spaces-for-delimeter NAME-field line-break-by-SSMS
      GID-field @NewLineChar
      three-spaces-for-delimeter NAME-field line-break-by-SSMS

      Like

  8. Hi Marko\All,
    Thanks for the response Marko..

    Your suggestions could be the way its works in the back ground.
    Is there a way to tell SSMS to NOT add any extra line breaks or delimiters?

    Otherwise we have to use Find & Replace or write an external script to clean up the extra white spaces.

    –Nilesh

    Like

  9. Go to Query Options (Query -> Query Options from the top menu bar). Select Results/Text from the tree on the left. Now you see combobox on the right with label “Output format”.

    Try and see what’s the best setting for you.

    Line breaks you’ll see in the output aren’t actuallu extra. They are the line breaks that your query returns in the second field.

    Like

  10. Thanks Marko. This helped.
    I Set the custom delimited to ~ and fed the results to another temp table that had an identity column.
    ————————
    1~
    ~ABC
    2~
    ~XYZ
    3~
    ~PQR
    ————————

    For odd rows used substring function to get rid of the last character.
    For even ones used to same to get rid of the first one.

    Did not use Replace since sometimes the result data can actually have ~ in it.

    Thanks for all the help on this one.

    –Nilesh

    Like

  11. Nice article.

    I did notice that if you simply

    Declare @var varchar (100)

    and

    SET @var = ‘SELECT
    111′

    Then the value of @var will be

    SELECT
    111

    thus including the new line.

    Alexander.

    Like

  12. Hey, Dave. I’ve been reading your blog for some time and I’ve pretty much gotten all the answers I’ve needed without having to ask any questions, but I’ve got one that’s stumping me.

    I’m pulling records from a SQL Server 2005 database via a SqlDataReader in C#, then rendering the output in HTML. When I render the output, all of the data is present, but there are no newlines in the output, which can make large blocks of text difficult to read. I just want the HTML to respect the newlines.

    I’ve tried replacing characters with several options, including CHAR(10) and CHAR(13) (and both together), as well as trying to commit direct HTML (i.e., ). Nothing is doing the trick. Do you have any ideas?

    Like

    • Hi,

      Try adding tag after every line you read from data reader. “\r\n” does not render as new line in HTML.

      Or write the data inside pre-tag with “\r\n” line delimiters which tells the browser to handle them as “real” new lines.

      Or better yet, databind your data source to, for example, GridView control.

      Like

  13. I would like to know if there is anyway in SQL Server 2005
    Management Studio’s query results to grid have | (pipe) while saving the resultset in .CSV file not the generic , (comma)

    Like

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

    Like

  15. 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 CR\LF 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?

    Like

      • 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 cr\lf (+’ ‘+) 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.

        Like

  16. 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

    Like

  17. 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?

    Like

  18. 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

    Like

  19. 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

    Like

  20. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  21. 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

    Like

  22. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

  23. 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

    Like

  24. 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.

    Like

  25. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s