SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code

Very simple and very effective. We use all the time for many reasons – formatting, while creating dynamically generated SQL to separate GO command from other T-SQL, saving some user input text to database etc.
DECLARE @strPrint VARCHAR(100);
SET @strPrint = 'Example of carriage return';
SET @strPrint = @strPrint + CHAR(13);
SET @strPrint = @strPrint + 'SQLAuthority.com';
PRINT @strPrint;
GO
PRINT '---------------------------------'
DECLARE @strPrint VARCHAR(100);
SET @strPrint = 'Example of new line feed';
SET @strPrint = @strPrint + CHAR(10);
SET @strPrint = @strPrint + 'SQLAuthority.com';
PRINT @strPrint;
GO

ResultSet:
Example of carriage return
SQLAuthority.com
———————————
Example of new line feed
SQLAuthority.com

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

85 thoughts on “SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code

  1. I enter data into SQL Server via ColdFusion. I”m aware of the CHAR(13) and CHAR(10) character usage via VisualBasic, but I cannot figure out how to construct the update query to incorporate them via ColdFusion.

    I tried to set up the variable something like this:

    myVar = “#firstInformation# + char(13) + char(10) + char(10) + #secondInformation#”

    But it doesn’t work. The whole thing translates like a string that encorporates the char information.

    Any ideas?

    Like

    • I suppose their difference is a matter of legacy. In most cases, you should use the one (or their combination) recognized by the “reader” application.

      For example, suppose you want to print in a .NET textbox a message stored in SQL Server. You should use their combination CHAR(13) + CHAR(10), as only their combination is recognized as a ‘new line character’ CR&LF by the .NET textbox control.

      I hope I helped

      Like

    • One is line feed and the other is carriage return. In UNIX you will need only line feed. In DOS (Windows) you need both. This comes from the typewriter. Line Feed – one line up at the same position and Carriage Return goes to the beginning of the line – Ravi

      Like

    • Instead of declare,how to use it with SELECT statement??I have a query with select statement …NameAddress so the address should come in next line.
      I have tried char (13) its not working.

      Like

  2. But
    In Microsoft SQL server 2005 Managerment Studio
    I want to enter a new line when I am writting content in table editor screen – .
    How do you Know? as s Hot key?
    Thanks so much !

    Like

  3. Hi,

    Large volume of data is retrieved from a Query[Select with 3 table joins]. Output has the LineFeed at the end of each Row.

    Actually I’m looking for inserting a Carraige Return at the end of each line along with the Line Feed.

    Please help me. Thank you,

    Like

  4. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  5. Hi All\Pinal,

    I wrote a Stored procedure using Char (10) + char(13)
    to get the data which we will be transmitting to different locations.According to their requirement i should get all the headings as below
    (Eg:

    CAL_INFO/—/—-/——//END
    CAL_DETAILS —/—-/—//END
    CAL_TEST/—/—/—/END
    )

    The output will be saved as a text file through our application.I couldn’t get the rows aligned as required also i’m getting boxes in the text file for some reason like the special characters….. can some one help meout with this…….

    Like

  6. Hi,
    my requirement is little bit complex, i want to return repeatation of columns as many as rows in table like
    if table is following
    phone userid
    222 1
    333 1
    444 1
    555 1

    and desired output should be
    userid phone1 phone2 phone3 phone4

    1 222 333 444 555

    plz suggest as i need it urgently.

    Like

  7. U can use COALESCE for that,

    Syntax:
    DECLARE @str varchar(10);
    SELECT @str = COALESCE(@str + ‘ ‘,”) + Convert(varchar,Phone) FROM

    Tope This solves u’r problem

    Like

  8. URGENT HELP:

    I am using the following code in ASP

    bdy = ” The Incident ” & objrse(“incident_id”) & ” was created on ” & objrse(“incident_datetime”) & “.”

    I need to replace with chr(13) & chr(10)

    I did the following:

    bdy = “chr(13) & chr(10) The Incident ” & objrse(“incident_id”) & ” was created on ” & objrse(“incident_datetime”) & “.”

    but it is not working.

    Can someone pls correct the syntax urgently

    Like

  9. To Alex, who was looking for a way to strip out the carriage returns from an expression, try this. It will strip out the carriage returns:

    select replace(source_column,char(10),”) from
    ……..
    where ……

    Like

  10. newbie question

    I am trying to do the reverse

    I need to concatenate some text and insert newlines in between, return the same in a stored procedure.

    I need to display the result on a coldfusion website.

    How do I insert the new lines?

    Like

  11. HI,

    I have a problem inserting new line in a string generated by “for xml”, trying to display a new line RDLC report field.

    Ho do i do this, Can anybody help

    Pradeep

    Like

  12. HI,

    I have a problem inserting new line in a string generated by “for xml”, trying to display a new line in RDLC report field.

    I don’t want to parse the string from c# code..

    How do i do this, through tsql.

    Thanks,
    Pradeep

    Like

  13. hai all
    i need a solution for the query using substring in sql

    DECLARE @SampleString VARCHAR(25)
    DECLARE @NewLineChar AS CHAR(2)
    SET @NewLineChar= CHAR(13) + CHAR(10)
    SELECT @SampleString =’1.1.0,1.2.1,1.3.2′
    select SUBSTRING(@SampleString,1,5) +@NewLineChar+
    SUBSTRING(@SampleString,7,5) +@NewLineChar+
    SUBSTRING(@SampleString,13,5)

    displays the output
    1.1.0 1.2.1 1.3.2 but i need the output as
    1.1.0
    1.2.1
    1.3.2

    Like

    • Hi Pinal,

      Im not able to create text file using the output string(with the use of xp_cmdshell) . if I break the string with char(13), it reflects only in result panel not in generated text file. if I break the string with char(10) im not able to create the file.

      Can u help me in this case.

      Thanks & Regards,
      Suresh.R

      Like

  14. Hi,

    When i run the following query, it display only part of results.
    Query
    DECLARE @strPrint NVARCHAR(4000);
    set @strprint=’Grant Select on glsetup to a.Solomon.Admin
    Grant Select on apdoc to a.Solomon.Admin
    Grant Select on apsetup to a.Solomon.Admin
    Grant Select on perclosed to a.Solomon.Admin
    Grant Select on batch to a.Solomon.Admin
    Grant Select on aptran to a.Solomon.Admin
    Grant Select on gltran to a.Solomon.Admin
    Grant Select on custom2 to a.Solomon.Admin
    Grant Select on pstatus to a.Solomon.Admin
    Grant Select on apadjust to a.Solomon.Admin
    Grant Select on rptcontrol to a.Solomon.Admin
    Grant Select on rptruntime to a.Solomon.Admin’
    select ‘Use ‘+ name + char(13)+ ‘go’ + char(13)+@strprint
    from sysdatabases where dbid =5
    order by dbid

    Results
    Use nsiplrup
    go
    Grant Select on glsetup to a.Solomon.Admin
    Grant Select on apdoc to a.Solomon.Admin
    Grant Select on apsetup to a.Solomon.Admin
    Grant Select on perclosed to a.Solomon.Admin
    Grant Select on batch to a.Solomon.Admin
    Grant Select on aptran

    (1 row(s) affected)

    It will not shown the entire results. How to do that

    Regards,
    Balavenkatesh

    Like

  15. Wonderful! I spent a good hour fighting with an SSIS to write out a flat file, went down numerous dead ends to get it to recognize the end of a row, and here you had the answer: + Char(13)+Char(10) at the end of my constructed single-column Select. Thank you!

    Like

  16. I am creating an XML string that stores a complete object in a table. Using the char(13) and char(10) make the xml print out properly formatted, but when I save it to a table I want to see the noprintable squares in the table. When I create and object in ASP.net, serialize it and store it as XML, I see the two little squares for the none printable characters. I can copy and past this from the table into Excel and it is properlly formated, but when I create the same string in sql using ‘string’ + chr(13)+chr(10)+ ‘string’ the CRand LF only shows up as white space. If I copy and paste this into Excel or notepad it shows as a single line, but is properly formatted if I select it to text.

    Like

  17. “What’s the difference between carriage return and line feed”?

    It goes back to the days of mechanical typewriters and teleprinters. Line feed moved the paper vertically one notch but did not move the print head left or right; carriage return moved the print head to the extreme left but did not move the paper. So you had to have both.

    These days, there’s much confusion. If you only use one of them, and write to a text file you will find that some programs don’t work, but others do. For example, it’s easy to get inconsistant behaviour between Notepad and Word by only using one. On the other hand many programs will work perfectly happily. And you can also get inconsistant behaviour if you use LF-CR rather than CR-LF.

    Like

  18. HI
    when i printing a series by while loop it show it in different rows but i wana this series in single line,
    Please send solution me ,its urgent

    thanks,
    Arvind

    Like

  19. Nice article!

    Stil this does not help when you want to use EXEC(@sql) if the script contains ‘GO’, as it seems the new lines are ignored in EXEC(@sql). Do you know any workarround for this?

    Like

  20. – use this variable while generating code
    DECLARE @WinNewLine nvarchar(2)
    set @WinNewLine = CONVERT ( nvarchar(2) , CHAR(13) + CHAR(10))

    — this is a horizontal tab
    DECLARE @Tab nvarchar(1)
    set @Tab = CONVERT(NVARCHAR(1) , CHAR(9))

    Like

  21. Hi,

    Some of the users in SQL Server 2005 face this error frequently. Login failed for user ”. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

    Please suggest what is the solution for this.

    REgards,
    S.Balavenkatesh

    Like

  22. Thanks!
    …But, do you know how to clean the messages.
    I want to clean : (1 row(s) affected) because my message with print command appears after those messagges(row affected).

    Like

  23. Thanks! This has helped. My testing of your code and results:
    SSMS 2008, T-sql
    —-
    DECLARE @strPrint VARCHAR(100);
    SET @strPrint = ‘Example of carriage return, then a line feed';
    SET @strPrint = @strPrint + CHAR(13)+CHAR(10);
    SET @strPrint = @strPrint + ‘SQLAuthority.com';
    PRINT @strPrint;
    GO
    PRINT ‘———————————‘
    DECLARE @strPrint VARCHAR(100);
    SET @strPrint = ‘Example of new line feed, then a carriage return';
    SET @strPrint = @strPrint + CHAR(10)+CHAR(13);
    SET @strPrint = @strPrint + ‘SQLAuthority.com, resulted in double space';
    PRINT @strPrint;
    GO
    DECLARE @WinNewLine nvarchar(2)
    DECLARE @Tab nvarchar(1)
    SET @WinNewLine = CONVERT(nvarchar(2),CHAR(13)+CHAR(10))/* reverse for dbl space*/
    SET @Tab = CONVERT(nvarchar(1),CHAR(9))
    PRINT ‘———————————‘
    DECLARE @strPrint VARCHAR(100);
    SET @strPrint = ‘Example of WinNewLine, with a Tab';
    SET @strPrint = @strPrint + @WinNewLine;
    SET @strPrint = @strPrint + @Tab + ‘SQLAuthority.com';
    PRINT @strPrint;
    GO

    Results:
    Example of carriage return, then a line feed
    SQLAuthority.com
    ———————————
    Example of new line feed, then a carriage return

    SQLAuthority.com, resulted in double space
    ———————————
    Example of WinNewLine, with a Tab
    SQLAuthority.com

    Like

  24. Hi,

    I’m using sql server 2005, and copying and pasting the results of the query below into notepad, and all the text is in one long line, not multiple lines, as I expect. The query is:

    DECLARE @NewLineChar AS CHAR(2)
    SET @NewLineChar= CHAR(13) + CHAR(10)

    DECLARE @Log varchar(max)
    SET @Log = ‘Log as of ‘ + convert(varchar(max), GetDate(), 101) + ‘.’ + @NewLineChar
    SET @Log = @Log + ‘———————————————–‘+ @NewLineChar

    Thanks,
    Shefali

    Like

  25. I need information regarding,how the sql server recognises the end of the statements if we are executing no of statements at a TIME.

    Like

  26. Hi,

    I want to know how can I find the carriage return from one of my Email_address column? The column is varchar(255) field.
    We have some records that someone added the carriage return in the e-mail address, I want to select those rows out from the table.

    Thanks

    Jenny

    Like

  27. Does anyone have an idea how to embed a PAGE BREAK in T-SQL?

    Line Breaks can be embedded with CHAR(13)+CHAR(10), but how about Page Breaks?

    I have an application which may be output to several devices which have different pagesizes. Originally, I thought about sending multiple blank lines, but that is not a solution since devices can have different page sizes.

    Any help will be appreciated.

    Thanks.

    Hopper44

    Like

  28. Hi,
    I am trying to generate a dynamic .ics file. I need true line breaks in it and using CHAR(13) + CHAR(10) doesn’t work. It just puts spaces in and then the .ics file is not valid. Any help would be greatly appreciated. I really need a line break with no spaces – sort of like does in html. Thanks!

    Like

    • Hi, I realized that I can select into variables and then print out what a need using CHAR(13) to get the line breaks I need in my .ics file. Thanks, Josephine

      Like

  29. Hi Pinal Dave,

    i am importing to access db file into sqlsever 2008 .
    in access db file one of the field name is amount in that column value is $(600.00) like that ,
    when i am trying to import into sqlserver 2008 i get an error ,
    in sqlserver i took data type is money ,in access db currency

    how to insert “$(600.00)” in sqlserver .

    thanks and regards
    suryagiri

    Like

  30. Just a note, you are going to need to use char(13) + char(10) if you want a newline to come through the sp_send_dbmail. Most email clients are requiring this sequence to register a newline.

    Like

  31. how about this one
    I would like to PREVENT a new line in the following type of codeing

    print a message
    do something
    print result

    so the output is allon one line

    e.g
    PRINT ‘restoring database : ‘
    RESTORE DATABAE ……..
    PRINT ‘success / failure’ depending on restore

    output woulr then be
    restoring database : success / failure

    any ideas?

    thanks in advance.
    Bryden

    Like

  32. sir i want export my database table data into a text file using sql server 2008

    ex: name no ph
    sri 123 12344
    234 4545 565

    like this format i want the result

    Like

  33. SELECT
    ‘:20:’+’test’+CHAR(13)+CHAR(10)+
    ‘:23B:CRED’+CHAR(13)+CHAR(10)+
    +’-}’

    This give a result in a single row. In sql server 2012 when I copy this content and paste in notepadd it gives 3 rows with CRLF. But In SQL Server 2005 when copied from a row and paste into notepad it shows in a single row. So How can I get the same result as in SQL Server 2012?

    Like

  34. in the above all post i am seeing the new character is printed but that using values from variables, does anybody having solution without using variables for ex: i have EmpID and EmpName from my Employee table
    and the i have 3 records are as
    EmpID EmpName
    ————————
    01 – Jhon
    02- Merry
    03- Love

    I want to display the 3 records as without using any variables….

    01
    Jhon
    ——-
    02
    Merry
    ——-
    03
    Love

    PLease reply i am waiting….for response… Thanks in advance…

    Like

  35. How can you find all the carriage return line feeds in a field in sql? I have a text field that has multiple lines and I’m looking for certain data on each line.

    Like

  36. Hi,

    I have list of sql statements which works fine when i run it in sybase IDE. When i call same batch of statements from my java code it gives me error as it makes all statemnts in 1 line when appended in string object of java. So i m trying to put char(10)+char(13) where i need new line. My orginal sql query is as below.

    SET TRANSACTION ISOLATION LEVEL 0
    GO

    SET NOCOUNT ON
    GO

    declare columnxyz cursor for
    select distinct columnxyz
    from db1..table1
    where col1 = ’20 May 2013′
    go

    declare @batchId varchar(3)
    declare @output varchar(100)

    Create table #Missed
    (
    maxReceivedMsg int,
    maxSummarizedMsg int,
    Status varchar(100) null
    )

    open columnxyz

    /*check if cursor is opened without error*/

    if @@sqlstatus = 1
    begin
    print ‘ERROR ACCESSING CURSOR…’
    return
    end

    java sql query in string is as below :-
    ———————————————–
    String getData1= “SET TRANSACTION ISOLATION LEVEL 0 + char(13) + char(10)+ GO + char(13) + char(10) “+
    “SET NOCOUNT ON + char(13) + char(10) + GO + char(13) + char(10) + declare columnxyz cursor for “+
    “select distinct columnxyz “+
    “from db1..table1 “+
    “wherecol1 = ’28 May 2013′ + char(13) + char(10)”+
    “GO + char(13) + char(10)”+
    “declare @batchId varchar(3) + char(13) + char(10)”+
    “declare @output varchar(100) + char(13) + char(10)”+
    ” Create table #Missed “+
    “( maxReceivedMsg int,maxSummarizedMsg int, Status varchar(100) null) + char(13) + char(10)”+
    “open columnxyz “+
    “if @@sqlstatus = 1 begin print ‘ERROR ACCESSING CURSOR…’ “+
    “return end”;

    What changes required in java string to make it work.

    Thanks in advance

    Like

  37. Hi,
    I am writing a records to a file,I want 2 column is printing in first line and rest of the line get printed on the next line onward.
    (note:from 3 column the the values are get inserted through loop structure)

    Like

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

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