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://blog.SQLAuthority.com)
pinaldave
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?
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
hi sir,
is there a way on how to remove the carriage return or line feed in ms sql 2000?
thank you.
Thanks for this post. It was exactly what I was looking for. :-)
What is the difference between CHAR(10) and CHAR(13)? Are there reasons to use either? Or should I simply just pick one?
CHAR(10) = Line Feed (LF)
CHAR(13) = Carriage Return (CR)
Awesome, thanks. Simple and effective.
Thanks so much. A big help!
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 !
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
its quite simple and effective..i got the answer wot am serchng 4..saves a lot of tym…thanx
Thanks for this post. It was exactly what I was looking for. :-)
Hi,
Large volume of data is retrieved from a Query[Select with 3 table joins]. Output has the LineFeed at the end of each Row.
Actually I’m looking for inserting a Carraige Return at the end of each line along with the Line Feed.
Please help me. Thank you,
[...] SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code [...]
Hi All\Pinal,
I wrote a Stored procedure using Char (10) + char(13)
to get the data which we will be transmitting to different locations.According to their requirement i should get all the headings as below
(Eg:
CAL_INFO/—/—-/——//END
CAL_DETAILS —/—-/—//END
CAL_TEST/—/—/—/END
)
The output will be saved as a text file through our application.I couldn’t get the rows aligned as required also i’m getting boxes in the text file for some reason like the special characters….. can some one help meout with this…….
Hi,
my requirement is little bit complex, i want to return repeatation of columns as many as rows in table like
if table is following
phone userid
222 1
333 1
444 1
555 1
and desired output should be
userid phone1 phone2 phone3 phone4
1 222 333 444 555
plz suggest as i need it urgently.
U can use COALESCE for that,
Syntax:
DECLARE @str varchar(10);
SELECT @str = COALESCE(@str + ‘ ‘,”) + Convert(varchar,Phone) FROM
Tope This solves u’r problem
URGENT HELP:
I am using the following code in ASP
bdy = ” The Incident ” & objrse(”incident_id”) & ” was created on ” & objrse(”incident_datetime”) & “.”
I need to replace with chr(13) & chr(10)
I did the following:
bdy = “chr(13) & chr(10) The Incident ” & objrse(”incident_id”) & ” was created on ” & objrse(”incident_datetime”) & “.”
but it is not working.
Can someone pls correct the syntax urgently