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
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………….