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)
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
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,
[...] SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code [...]
Hi ,
Adding New Line Character is not working When we do Export to Excel from SSRS
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
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?
It is the opposite of what Vishal Sharma is trying to do.
He has new lines already and is trying to remove them.
Hi Pinal,
I got stuck on this task, but the article help me out.
Thanks,
Raj Mahida
HI,
I have a problem inserting new line in a string generated by “for xml”, trying to display a new line RDLC report field.
Ho do i do this, Can anybody help
Pradeep
HI,
I have a problem inserting new line in a string generated by “for xml”, trying to display a new line in RDLC report field.
I don’t want to parse the string from c# code..
How do i do this, through tsql.
Thanks,
Pradeep
Very well explained.
To bad SQL Server doesn’t allow (in T-SQL) the construction of strings like C or Java with the \n sintaxe.
Nevertheless this is a good tip.
Thanks,
Joaquim.
hai all
i need a solution for the query using substring in sql
DECLARE @SampleString VARCHAR(25)
DECLARE @NewLineChar AS CHAR(2)
SET @NewLineChar= CHAR(13) + CHAR(10)
SELECT @SampleString =’1.1.0,1.2.1,1.3.2′
select SUBSTRING(@SampleString,1,5) +@NewLineChar+
SUBSTRING(@SampleString,7,5) +@NewLineChar+
SUBSTRING(@SampleString,13,5)
displays the output
1.1.0 1.2.1 1.3.2 but i need the output as
1.1.0
1.2.1
1.3.2
Hi Senjay,
In SSMS view the result in text be setting Query > Results to > Results to Text
Line feed not appear in grid.
Regards,
Pinal Dave
Hi Pinal,
Im not able to create text file using the output string(with the use of xp_cmdshell) . if I break the string with char(13), it reflects only in result panel not in generated text file. if I break the string with char(10) im not able to create the file.
Can u help me in this case.
Thanks & Regards,
Suresh.R
Note that although it is not shown exactly how it is shown in query window, carriage return will be added as part of text
Hi,
When i run the following query, it display only part of results.
Query
DECLARE @strPrint NVARCHAR(4000);
set @strprint=’Grant Select on glsetup to a.Solomon.Admin
Grant Select on apdoc to a.Solomon.Admin
Grant Select on apsetup to a.Solomon.Admin
Grant Select on perclosed to a.Solomon.Admin
Grant Select on batch to a.Solomon.Admin
Grant Select on aptran to a.Solomon.Admin
Grant Select on gltran to a.Solomon.Admin
Grant Select on custom2 to a.Solomon.Admin
Grant Select on pstatus to a.Solomon.Admin
Grant Select on apadjust to a.Solomon.Admin
Grant Select on rptcontrol to a.Solomon.Admin
Grant Select on rptruntime to a.Solomon.Admin’
select ‘Use ‘+ name + char(13)+ ‘go’ + char(13)+@strprint
from sysdatabases where dbid =5
order by dbid
Results
Use nsiplrup
go
Grant Select on glsetup to a.Solomon.Admin
Grant Select on apdoc to a.Solomon.Admin
Grant Select on apsetup to a.Solomon.Admin
Grant Select on perclosed to a.Solomon.Admin
Grant Select on batch to a.Solomon.Admin
Grant Select on aptran
(1 row(s) affected)
It will not shown the entire results. How to do that
Regards,
Balavenkatesh
Wonderful! I spent a good hour fighting with an SSIS to write out a flat file, went down numerous dead ends to get it to recognize the end of a row, and here you had the answer: + Char(13)+Char(10) at the end of my constructed single-column Select. Thank you!
I am creating an XML string that stores a complete object in a table. Using the char(13) and char(10) make the xml print out properly formatted, but when I save it to a table I want to see the noprintable squares in the table. When I create and object in ASP.net, serialize it and store it as XML, I see the two little squares for the none printable characters. I can copy and past this from the table into Excel and it is properlly formated, but when I create the same string in sql using ‘string’ + chr(13)+chr(10)+ ‘string’ the CRand LF only shows up as white space. If I copy and paste this into Excel or notepad it shows as a single line, but is properly formatted if I select it to text.
Never mind. I figures out that when I insert the text into a field of type text everything works correctly
“What’s the difference between carriage return and line feed”?
It goes back to the days of mechanical typewriters and teleprinters. Line feed moved the paper vertically one notch but did not move the print head left or right; carriage return moved the print head to the extreme left but did not move the paper. So you had to have both.
These days, there’s much confusion. If you only use one of them, and write to a text file you will find that some programs don’t work, but others do. For example, it’s easy to get inconsistant behaviour between Notepad and Word by only using one. On the other hand many programs will work perfectly happily. And you can also get inconsistant behaviour if you use LF-CR rather than CR-LF.
How can i insert a newline character data from a multiline text box to data field in SQL2000 after every 100 characters.
Nice, simple and effective. Thanks!
HI
when i printing a series by while loop it show it in different rows but i wana this series in single line,
Please send solution me ,its urgent
thanks,
Arvind
Nice article!
Stil this does not help when you want to use EXEC(@sql) if the script contains ‘GO’, as it seems the new lines are ignored in EXEC(@sql). Do you know any workarround for this?
– use this variable while generating code
DECLARE @WinNewLine nvarchar(2)
set @WinNewLine = CONVERT ( nvarchar(2) , CHAR(13) + CHAR(10))
– this is a horizontal tab
DECLARE @Tab nvarchar(1)
set @Tab = CONVERT(NVARCHAR(1) , CHAR(9))
Thx, Yordan for adding the tab, just what I needed!
Hi,
Some of the users in SQL Server 2005 face this error frequently. Login failed for user ”. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
Please suggest what is the solution for this.
REgards,
S.Balavenkatesh
it is not working with SQL Server Compact : 3.5.5386.0
Pls Help
Try using convert(nvarchar(1),0x0D) + convert(nvarchar(1),0x0A) instead of char(13) + char(10)
GREAT JOB………….
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 http://beyondrelational.com/modules/2/blogs/70/posts/10798/bcp-export-data-to-text-file.aspx
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.