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

Feed on: Posts; Comments

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

August 22, 2007 by pinaldave

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://www.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 12 Comments

12 Responses to “SQL SERVER - T-SQL Script to Insert Carriage Return and New Line Feed in Code”

on August 25, 2007 at 7:30 pm Karen Little

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?
on August 31, 2007 at 6:56 am Ameen Oluajayi

In coldfusion it is ‘CHR’ not ‘CHAR’ so use chr

Also lose the plus (+) signs (ampersand &) is the concate symbol in CF.

so:

OR without quotes
on December 20, 2007 at 6:25 pm alex

hi sir,

is there a way on how to remove the carriage return or line feed in ms sql 2000?

thank you.
on February 7, 2008 at 7:41 pm Kerry Wano

Thanks for this post. It was exactly what I was looking for. :-)
on April 29, 2008 at 10:52 pm Perry

What is the difference between CHAR(10) and CHAR(13)? Are there reasons to use either? Or should I simply just pick one?
on May 1, 2008 at 11:24 pm vincent

CHAR(10) = Line Feed (LF)
CHAR(13) = Carriage Return (CR)
on May 8, 2008 at 7:23 pm Adam

Awesome, thanks. Simple and effective.
on June 24, 2008 at 10:48 am Chad

Thanks so much. A big help!
on September 6, 2008 at 8:29 am Tea

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 !
on November 3, 2008 at 7:15 pm Shailesh

This was very useful Man ! Its easy to miss such small things. Reading this article saved me a lot of Time. So Thanks and keep up the Good Work

Shailesh
on November 29, 2008 at 2:17 pm muneer

its quite simple and effective..i got the answer wot am serchng 4..saves a lot of tym…thanx
on December 29, 2008 at 6:45 am Raj

Thanks for this post. It was exactly what I was looking for. :-)