CHAR(13) in SELECT statement

how can you pad char(13) (ENTER Key) in sql server? when there is an output that could span more than one line?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

25112Author Commented:
example:
  DECLARE @MSG VARCHAR(4000)
   create table test (TransDt datetime, col1 varchar(1000))
   
   insert into test
   select getdate() TransDt,'first' col1 union
   select getdate()-1,'second' union
   select getdate()-2,'third' union
   select getdate()-3,'fourth'
   
   
  SELECT @MSG = @MSG + CHAR(13)+ CAST(TransDt as varchar(50))+' # '+ col1 FROM test
     SELECT @MSG
     
     
   drop table test
0
COANetworkCommented:
Define the term "pad".  25112's suggestion will add a single empty line above your resultset (by the way - you have to assign initial value to your @MSG variable or it will not work):  
DECLARE @MSG VARCHAR(4000) = ''

Open in new window

.  Maybe you are looking for ability to insert a specified number of carriage returns into a resultset?  Then the code would be something like this:
select '***' + REPLICATE(CHAR(13), 10) + '***'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
thanks- i set
SET @MSG = ''
now
----------------------------
DECLARE @MSG VARCHAR(4000)
SET @MSG = ''
   create table test (TransDt datetime, col1 varchar(1000))
   
   insert into test
   select getdate() TransDt,'first' col1 union
   select getdate()-1,'second' union
   select getdate()-2,'third' union
   select getdate()-3,'fourth'
   
  SELECT @MSG = @MSG + CHAR(13)+ CAST(TransDt as varchar(50))+' # '+ col1 FROM test
     SELECT @MSG
 
   drop table test
---------------

the above gives me
 Oct 18 2013 11:46AM # fourth Oct 19 2013 11:46AM # third Oct 20 2013 11:46AM # second Oct 21 2013 11:46AM # first

rather i want
    Oct 18 2013 11:46AM # fourth
    Oct 19 2013 11:46AM # third
    Oct 20 2013 11:46AM # second
    Oct 21 2013 11:46AM # first
   
    stored in the variable, which i will then send as the message of an email through sp_send_dbmail. thanks.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

COANetworkCommented:
 SELECT @MSG = @MSG + CHAR(11) + CHAR(13)+ CAST(TransDt as varchar(50))+' # '+ col1 FROM test

Open in new window

 Try this.  CHAR(11) is line feed
0
Scott PletcherSenior DBACommented:
SQL Server's line feed is CHAR(10) -- use that when you need to advance to the next line.
0
25112Author Commented:
COANetwork, adding that line gives me:
Oct 18 2013  1:00PM # fourth  Oct 19 2013  1:00PM # third  Oct 20 2013  1:00PM # second  Oct 21 2013  1:00PM # first
0
25112Author Commented:
scott,
all of the below give me answer in one line...

   select '1'+CHAR(10)+'2'
   select '1'+CHAR(11)+'2'
   select '1'+CHAR(13)+'2'
0
COANetworkCommented:
just a silly question - are you viewing your results as an Outlook plain-text e-mail?  is that wehre you get your "one line"..?  Remember that Outlook notifies you when it has "removed extra line breaks" from text, and you have to click the yellow warning label to restore them and view original text.  I don't know how Microsoft determines wht is "extra", but it is a pain in the rear.  please verify that your results are not getting butchered by your mail program.
0
25112Author Commented:
COA, right now I am just talking about SQL Server..

please try
   select '1'+CHAR(10)+'2'
GO
   select '1'+CHAR(11)+'2'
GO
   select '1'+CHAR(13)+'2'

how cna we get the line breaks to show up in these.. we can then take it into the code.
0
Scott PletcherSenior DBACommented:
Press "Ctrl-T", then run the SELECTs and you'll see the "lined" results.

By default you're looking at "grid" results, which don't reflect line breaks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.