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
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).
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
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
I need information regarding,how the sql server recognises the end of the statements if we are executing no of statements at a TIME.
If you have hightlighted the codes, it recognises based on the highlight. If not, based on the end of line
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
Try something like this:
select * from [customer information] where [email_address] like ‘%’ + convert(nvarchar(1),0x0D) + ‘%’
where col like ‘%’+char(10)+char(13)+’%’
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
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!
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
Thanks a lot to all you guys. It has helped solved my problem
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
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.
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
Great …………
Thanks
Thanks pinal,
Your post always help me to find easiest and understandable solution.
Keep doing good things,,, have a nice day,,,
Nice Article ya.. i search for this only…
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
thank u sir
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
Use bcp
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?
In the Query builder result, set it to text mode and copy the result from there
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…
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.