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
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.
select replace(replace(col,char(13),”),char(10),”) from your_table
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?
I suppose their difference is a matter of legacy. In most cases, you should use the one (or their combination) recognized by the “reader” application.
For example, suppose you want to print in a .NET textbox a message stored in SQL Server. You should use their combination CHAR(13) + CHAR(10), as only their combination is recognized as a ‘new line character’ CR&LF by the .NET textbox control.
I hope I helped
One is line feed and the other is carriage return. In UNIX you will need only line feed. In DOS (Windows) you need both. This comes from the typewriter. Line Feed – one line up at the same position and Carriage Return goes to the beginning of the line – Ravi
Instead of declare,how to use it with SELECT statement??I have a query with select statement …NameAddress so the address should come in next line.
I have tried char (13) its not working.
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 !
Hi Tea,
Did you ever get an answer on what the key combination is for adding a line break in the open table view in SSMS? I know there is one, but I forgot :(
Thanks,
Edward
It is not advisable to use this method to enter data
Use query analyser and INSERT statement
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,
Hi AllPinal,
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
To Alex, who was looking for a way to strip out the carriage returns from an expression, try this. It will strip out the carriage returns:
select replace(source_column,char(10),”) from
……..
where ……
newbie question
I am trying to do the reverse
I need to concatenate some text and insert newlines in between, return the same in a stored procedure.
I need to display the result on a coldfusion website.
How do I insert the new lines?
@peggy
I do not understand. How is that the reverse?