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

  • 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

    Reply
  • harish rajaraman
    August 23, 2013 4:47 pm

    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)

    Reply
  • How to generate a script with data for existing tables in sql server 2005 using query in jdbc and using database name

    Reply
  • Please help me as soon as possible !!! Thanks in advance..

    Reply
  • plzzzzzzzzzzzzzz help mee

    Reply
  • William Benton
    May 30, 2014 1:45 am

    When using the output in Excel, you need to use the CHAR(10) to make the carriage returns show within a cell. The CHAR(13) has no effect within a cell in Excel.

    Reply
  • DECLARE @strPrint VARCHAR(100);
    DECLARE @cmd VARCHAR(150);
    SET @strPrint = ‘Example of new line feed’;
    SET @strPrint = @strPrint + CHAR(13);
    SET @strPrint = @strPrint + ‘SQLAuthority.com’;
    PRINT @strPrint;
    SET @cmd = ‘echo>E:\at.txt ‘+@strPrint
    EXEC master..xp_cmdshell @cmd, no_output

    output:
    Example of new line feedSQLAuthority.com
    output for char(10):
    Example of new line feed

    i would like to write output to text file using above query , then i got text in textfile like above,
    can you provide the solution to write carriage return and line feed to text file,
    means in text file the output like below
    Example of new line feed
    SQLAuthority.com

    for getting above outptu what to do kindly suggest
    Thanks

    Reply
  • Chris Valdivia
    June 9, 2015 11:09 pm

    I know this blog post is old old old, but THANK YOU for having it available!

    Reply
  • Thank you Dave very clear and concise. Very good to know.

    Reply
  • Steven Jones
    May 2, 2017 11:18 pm

    Thanks Dave. I used this a function to format a long input query into a more read-able, formatted dynamic sql statement, as you suggested.

    Reply
  • That’s great to hear. Thanks!

    Reply
  • ‘This is line 1.’ + CHAR(13)+CHAR(10) + ‘This is line 2.’

    Reply

Leave a Reply

Menu