September 28, 2007 at 2:04 pm
Greetings all. I have the following function, thanks to Jeff Moden's most gracious help.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER FUNCTION dbo.fnCSV
(
@EWO VARCHAR(20)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return+'|~~| ','') + roadblock
FROM tblroadblocks
WHERE ewo = @ewo
RETURN @Return
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I call it fnCSV, but I change the delimiters depending on circumstance. In this case I chose |~~| because the field 'roadblock' is used to enter text, and I wanted something that was easily visible. My question is how can I make each roadblock appear on a new line within the cell in the result row. Is that even possible?
Greg Snidow
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 28, 2007 at 2:48 pm
Not sure what you mean by 'within the cell in the result row' but you can use the char() function to insert formatting codes into any string.
New code:
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return+'|~~| ','') + roadblock + CHAR(13) + CHAR(10)
FROM tblroadblocks
WHERE ewo = @ewo
RETURN @Return
END
those 2 will give you a carriage return + line feed which when displayed will translate into a newline
gl
m
September 28, 2007 at 8:01 pm
This is presentation stuff. In the best circonstances, this should be handled by the application layer. If not possible in this case, then use the replace option.
October 1, 2007 at 10:39 am
Thanks mjw. It does not work for what I am doing. I tried using the char function in various positions in the function to no avail. I am simply using a linked spreadsheet to display the data. Its no biggie, users can live with it.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 2, 2007 at 1:16 pm
Have you tried two CRLFs instead of just one?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply