add cariage return

I'm trying to add a carriage return in the below function, but nothing is
happening.
The end result is to cut-n-paste into notepad with carriage returns.
What am I doing wrong?
thanks!
CREATE FUNCTION dbo.fctConcatTitles
(
@.O VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.r VARCHAR(8000)
SELECT @.r = ISNULL(@.r+ Char(13) , '') + Title + ' - ' + Artist
FROM Titles
WHERE OrderNo = @.O
RETURN @.r
ENDshank wrote on Wed, 11 Jan 2006 05:15:01 -0500:

> I'm trying to add a carriage return in the below function, but nothing is
> happening.
> The end result is to cut-n-paste into notepad with carriage returns.
> What am I doing wrong?
Try CHAR(13) + CHAR(10). Windows uses a Carriage Return + Line Feed for end
of line termination, not just a CR.
Dan|||The above code works and an optional solution is manually adding a
linefeed,
like this:
SELECT @.r = ISNULL(@.r + '
' , '') + Title + ' - ' + Type
FROM Titles
It's not pretty - but efficient...
/ola|||shank (shank@.tampabay.rr.com) writes:
> I'm trying to add a carriage return in the below function, but nothing is
> happening.
> The end result is to cut-n-paste into notepad with carriage returns.
> What am I doing wrong?
> thanks!
> CREATE FUNCTION dbo.fctConcatTitles
> (
> @.O VARCHAR(32)
> )
> RETURNS VARCHAR(8000)
> AS
> BEGIN
> DECLARE @.r VARCHAR(8000)
> SELECT @.r = ISNULL(@.r+ Char(13) , '') + Title + ' - ' + Artist
> FROM Titles
> WHERE OrderNo = @.O
> RETURN @.r
> END
Beside the CR issue, note that this piece of code relies on undefined
behaviour, so there is no guarantee that you will get the result you are
looking for.
In SQL 2000, the only guaranteed way is to run a cursor. And most
probably you want an ORDER BY as well, so that you don't the data
in some funny order.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I've tried this...
SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + Title + ' - ' + Artist
Then this...
SELECT @.r = ISNULL(@.r+ '
', '') + Title + ' - ' + Artist
...without any luck.
To be clear, I'm expecting to see results in QA.
I'm also cut-n-pasting from QA into notepad and there's no carriage returns.
Can you explain more about the cursor?
I don't believe I've ever used code that uses the cursor.
thanks to all!
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974881545AC39Yazorman@.127.0.0.1...
> shank (shank@.tampabay.rr.com) writes:
> Beside the CR issue, note that this piece of code relies on undefined
> behaviour, so there is no guarantee that you will get the result you are
> looking for.
> In SQL 2000, the only guaranteed way is to run a cursor. And most
> probably you want an ORDER BY as well, so that you don't the data
> in some funny order.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Use the "Results to Text" option.
ML
http://milambda.blogspot.com/|||shank (shank@.tampabay.rr.com) writes:
> I've tried this...
> SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + Title + ' - ' + Artist
> Then this...
> SELECT @.r = ISNULL(@.r+ '
> ', '') + Title + ' - ' + Artist
> ...without any luck.
> To be clear, I'm expecting to see results in QA.
> I'm also cut-n-pasting from QA into notepad and there's no carriage
> returns.
To echo ML's post: you are using text more, aren't you? In grid mode
you will not see any CRs.

> Can you explain more about the cursor?
> I don't believe I've ever used code that uses the cursor.
That's good! Too many inexperienced programmers use cursors when they
shouldn't, so I almost feel bad for showing you, but since this only
works with cursors (in SQL 2000), there is not much choice:
DECLARE @.r varchar(8000),
@.item varchar(50)
DECLARE thiscur CURSOR LOCAL FAST_FORWARD FOR
SELECT title + '-' + artist
FROM Titles
WHERE OrderNo = @.OrderNo
ORDER BY title, artist
OPEN thiscur
WHILE 1 = 1
BEGIN
FETCH thiscur INTO @.item
IF @.@.fetch_status <> 0
BREAK
SELECT @.r = CASE WHEN @.r IS NULL THEN ''
ELSE @.r + char(10) + char(13)
END + @.item
END
DEALLOCATE thiscur
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog:
> To echo ML's post: you are using text mode, aren't you? In grid mode
> you will not see any CRs
Well that's not true is it? The CRs is shown as "squares" in grid mode
and when c-n-p
from QA to notepad they tag along and do result in CRs.
shank:
I cannot understand why you don't get the expected result - can you try
the
following code (NB you have to have example database "pubs" installed)
-- code start
use pubs
declare @.r varchar(8000)
SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + title + ' - ' + type
>From titles
Select @.r
-- code end
/o