carriage return in a string function

[dolodolo]

Hi,

I'm trying to insert a carriage return into the following string function but it still returns all the records on the same line:


SELECT @str = COALESCE(@str + char(13),'')
+ '$'
+ CAST ( t2.stotalAmount AS VARCHAR)
+ ' for '
+ CAST( rtrim(t2.snotes) AS VARCHAR)

Any help would be much appreciated.

Thanks

 

[ousoonerjoe]

Don't forget to add the LineFeed, CHAR(10) with the Carriage Return...

SET @String = 'Some' + CHAR(10) + CHAR(13) + 'text here';

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[dolodolo]

Thanks very much!

 

[simian336]

I think you code is working fine. You just won't see it on seperate lines unless you run it to a text window.

declare @str varchar(50)
set @str='This is a test'

SELECT @str = COALESCE(@str + char(13),'')
SELECT @str = @str + 'bla'

print @str

Simi

 

[gmmastros]

I agree with simian336. The alternative to 'print @str' is to set your output to text (instead of grid).

In SSMS, click tools -> Options.
Click "Query Results"
Change "Default destination for results" to "results to text"
expand "Query Results"
Expand "SQL Server"
Click "Results to text"

Notice there are some options you can specify for how the results are displayed. Pay particular attention to "Maximum number of characters displayed in each column". You may need to change this.

Lastly, realize that these settings are specifically for the SQL Server Management Studio. SSMS is NOT the same thing as the database engine itself. If you change the settings here, it ONLY applies to SSMS, and not to any other application you may use to connect to the database.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dolodolo]

Thanks guys - that works!