Carriage Return in a STUFF Statement ??
-
Hi,
I found the code below
SELECT STUFF(
(SELECT CHAR(13),CONCAT(tel_type.Name, ': ', tel.[Phone Number])
FROM dbo.PhoneNumbers AS tel
INNER JOIN dbo.PhoneNumberTypes AS tel_type ON tel_type.ID = tel.Type
FOR XML PATH('')
), 1, 0, '') AS Phones;
to have carriage return between each value but I'm getting
Home: (999) 111-1111 Mobile: (999) 555-5555
Would like :
Home: (999) 111-1111
Mobile: (999) 555-5555
Thanks
Joe
- This topic was modified 4 years, 1 month ago by jbalbo.
-
if you output onto a file or to text it should give you the desired result
if you are querying on SSMS to the grid it will remove those.
-
Thanks for getting back, I'm using SSRS , the output looks the same, maybe something I need to format in the textbox?
-
I've found that SOMETIMES you need a CHAR(10) AND CHAR(13) before it puts in a new line. Other times, I've found that having both confuses the tool representing the data and it ignores the new line OR it converts it to weird characters.
I am not sure how SSRS handles it, but might not hurt to try putting both in.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
I don't know for sure but, IIRC, SSRS renders out as HTML. That might mean you need to use <br> instead of an actual character to induce a break in the output.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - frederico_fonseca wrote:
if you output onto a file or to text it should give you the desired result
if you are querying on SSMS to the grid it will remove those.
If you go into SSMS menu option Query\Options\Grid you can make it show carriage when you copy/paste from the grid.
-
Jonathan... they didn't say so in the original post but the OP is trying to do this in SSRS, not SSMS.
Great tip for SSMS, though!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Everyone for the tips,
I kind of hacked it up a bit....
Changed the CHAR13 to a "&" encompassed the STUFF in a LEFT command to subtract the last "&"
Then in SSRS used a REPLACE statement with a Chr(10)
=Replace(Fields!Phones.Value,"&", Chr(10))
It seems to work OK....
Thanks
-
Try this:
Select stuff((Select char(10) + concat(tel_type.Name, ': ', tel.[Phone Number])
From dbo.PhoneNumbers As tel
Inner Join dbo.PhoneNumberTypes As tel_type On tel_type.ID = tel.Type
For xml Path('')), 1, 0, '') As Phones;SSRS recognizes CHAR(10) as a line feed and will wrap each row in the text box. Or - just use concat for everything:
Select stuff((Select concat(char(10), tel_type.Name, ': ', tel.[Phone Number])
From dbo.PhoneNumbers As tel
Inner Join dbo.PhoneNumberTypes As tel_type On tel_type.ID = tel.Type
For xml Path('')), 1, 0, '') As Phones;Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams wrote:
SSRS recognizes CHAR(10) as a line feed and will wrap each row in the text box.
That's good to know. Thanks, Jeffrey.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Scott? 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams wrote:
Scott? 🙂
Sorry... had a "Scott" on my mind from a different post. Corrected my previous post.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Jeffrey Williams wrote:
Scott? 🙂
Sorry... had a "Scott" on my mind from a different post. Corrected my previous post.
🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply