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. Let us learn about T-SQL Script to Insert Carriage Return and New Line Feed in Code.

SQL SERVER -  T-SQL Script to Insert Carriage Return and New Line Feed in Code csv-800x211

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

A CSV is a comma separated values file, which allows data to be saved in a table structured format. CSV files are plain-text files, which makes them easy for the website developer to create. Because the CSV is plain-text, it makes the data easy to import into any spreadsheet program or database regardless of what type of computer or software program you are using.  CSVs look like a garden-variety spreadsheet, but with a .csv extension.

Let me know your thoughts about this blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – 2005 – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
Next Post
SQL SERVER – 2005 – Use of Non-deterministic Function in UDF – Find Day Difference Between Any Date and Today

Related Posts

90 Comments. Leave new

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

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

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

    Reply
  • sri lakshmi kadi
    December 27, 2010 1:03 pm

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

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

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

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

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

      Reply
  • Bart Madriaga
    March 9, 2011 9:43 am

    Thanks a lot to all you guys. It has helped solved my problem

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

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

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

    Reply
  • Great …………
    Thanks

    Reply
  • Thanks pinal,
    Your post always help me to find easiest and understandable solution.
    Keep doing good things,,, have a nice day,,,

    Reply
  • Nice Article ya.. i search for this only…

    Reply
  • if you don’t know the result of my question just say i don’t know that’s it please don’t show this type of answeres

    Reply
  • thank u sir

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

    Reply
  • Hari Saran Manandhar
    January 28, 2013 2:23 pm

    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?

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

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

    Reply

Leave a Reply

Menu