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.
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)
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
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)
How to generate a script with data for existing tables in sql server 2005 using query in jdbc and using database name
Please help me as soon as possible !!! Thanks in advance..
plzzzzzzzzzzzzzz help mee
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.
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
I know this blog post is old old old, but THANK YOU for having it available!
Thank you Dave very clear and concise. Very good to know.
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.
That’s great to hear. Thanks!
‘This is line 1.’ + CHAR(13)+CHAR(10) + ‘This is line 2.’