Add a carriage return within text

Question

Post reply

Add a carriage return within text

littlelisa1111

SSChasing Mays

Points: 617
More actions
November 8, 2011 at 11:51 am

#390799

SELECT id, CASE WHEN Subject IS NULL THEN 'Change to HR System' ELSE '' END AS SubjectDesc, Email, FirstName, LastName, ChangeDate, OldStatus, NewStatus,
OldTitle, OldDepartment, OldSupervisor, CASE WHEN NewTitle IS NOT NULL THEN 'HR Change for: '+ CAST(FirstName + ' ' + LastName AS varchar(100))+', Previous Title: '+ CAST(OldTitle AS varchar(800))+', New Title: '+ CAST(NewTitle AS varchar(800)) ELSE '' END AS UpdateTitleDesc,
FROM dbo.HRChangeLog
How do I add a carriage return in this line after the name?
CASE WHEN NewTitle IS NOT NULL THEN 'HR Change for: '+ CAST(FirstName + ' ' + LastName AS varchar(100))+', Previous Title: '+ CAST(OldTitle AS varchar(800))+', New Title: '+ CAST(NewTitle AS varchar(800)) ELSE '' END AS UpdateTitleDesc,
Any help would be greatly appreciated. Thank you.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

Add CHAR(13)

Ex:

CASE WHEN NewTitle IS NOT NULL THEN 'HR Change for: '+ CAST(FirstName + ' ' + LastName AS varchar(100))+ CHAR(13) + ', Previous Title: '+ CAST(OldTitle AS varchar(800))+', New Title: '+ CAST(NewTitle AS varchar(800)) ELSE '' END AS UpdateTitleDesc,

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

littlelisa1111 SSChasing Mays Points: 617 More actions · Answer 2

I still can't get it to work. I copied and pasted the code and it still came out in one line?? :unsure:

[font="Arial Black"]HR Change for: John Doe, Previous Title: Admin, New Title: Developer[/font]

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

Are you outputting te results to grid or text?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

littlelisa1111 SSChasing Mays Points: 617 More actions · Answer 4

The results are in a grid. I'm using SQL Server Management Studio if that makes a difference. My goal is once I get this to work to create a job to insert the output into the description of another table which will then create a task to send to the appropriate office.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Try adding a line feed CHAR(10) immediatel before or after the carriage return

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Lowell SSC Guru Points: 323506 More actions · Answer 6

littlelisa1111 (11/8/2011)
The results are in a grid. I'm using SQL Server Management Studio if that makes a difference. My goal is once I get this to work to create a job to insert the output into the description of another table which will then create a task to send to the appropriate office.

grid mode NEVER shows CrLf, it converts them to spaces for display purposes only..

the CrLf may exist in the data, but you have to switch to TextMode to see it.

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!

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 7

You CANNOT view this in grid mode. Each line in the grid output is a new "ROW".

See for your self, Try this:

-- Run this in "Results to Text"

SELECT 'This!' + CHAR(13) + 'is ' + CHAR(13) + 'test'

It appears to return all one line of text correct?

Now, change to "Results to Text" and try this:

-- Run this in "Results to Text"

SELECT 'This!' + CHAR(13) + 'is ' + CHAR(13) + 'test'

-- Execute this as-is

DECLARE @BODY varchar(150), @Subject varchar(50)

SET @Subject = 'This!'

SET @BODY = 'This!' + CHAR(13) + 'is ' + CHAR(13) + 'test'

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'mydoggiejessie@abc.com',

@subject = @Subject,

@body = @Body

Not only is your first SELECT statement wrapped on different lines, the email you'll receive text body is also wrapped...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

littlelisa1111 SSChasing Mays Points: 617 More actions · Answer 8

Thanks Lowell! I was now able to see the carriage returns.

littlelisa1111 SSChasing Mays Points: 617 More actions · Answer 9

@mydoggiejessie Thank you! the Char(13) worked, I just couldn't see it

daroberts2 SSC Rookie Points: 48 More actions · Answer 10

Change Options (Query Results, SQL Server, Results to Grid, "Retain CR/LF on copy or save")

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 11

daroberts2 - Tuesday, March 13, 2018 8:23 AM
Change Options (Query Results, SQL Server, Results to Grid, "Retain CR/LF on copy or save")

Now, store that in a table without human intervention. 😉

--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)