Using COALESCE Statement And Char(13)
-
Hi,
I'm working on a procedure where a list of unpaid members will appear in a single field with values separated by a pipe delimiter. The COALESCE statement was working fine until they asked to have the names appear on a separate line each in this varchar(max) field. So I added char(13) at the end of the statement.
Everything works fine EXCEPT the names still appear in one long string in the field. I'm using char(13) which should be fine.
If anyone has any suggestions, that would be great.
-- Part of the code:
SELECT @ChapterRosterText = COALESCE(@ChapterRosterText,'') + full_name + '|' + CASE WHEN PHONE IS NULL Then '' Else PHONE END+ '|' + CASE WHEN EMAIL IS NULL THEN '' ELSE EMAIL END + char (13)
FROM #ProcessUnpaidMembers
WHERE CHAPTER = @Chapter
Update Unpaid_Members
Set Roster_Text = @ChapterRosterText
Where Chapter = @CHAPTER
Thank you.
Roger
-
Try this:
SELECT @ChapterRosterText = COALESCE(@ChapterRosterText,'') + full_name + '|' + CASE WHEN PHONE IS NULL Then '' Else PHONE END+ '|' + CASE WHEN EMAIL IS NULL THEN '' ELSE EMAIL END + char(13) + char(10)
FROM #ProcessUnpaidMembers
WHERE CHAPTER = @Chapter
Update Unpaid_Members
Set Roster_Text = @ChapterRosterText
Where Chapter = @CHAPTER
-
how are you looking at the results?
you know that SSMS, in gridview mode, converts CrLf to spaces so that it can present all the data, right? even thought the actual data contains your Cr /CrLf?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Where are you viewing this that it is all on one line? If it is in SSMS and you are viewing results as grid it will not span multiple lines. If that is the case just switch your output to text and it should show on multiple lines.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Looks like Lowell beat me to it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
OMG, that was it! Can't believe I didn't think of it.
Thank you!
-
Roger Abram (5/3/2012)
OMG, that was it! Can't believe I didn't think of it.Thank you!
What was it?
-
Oh, in Management Studio I needed to view the results in text rather than grid mode.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply