Problem with formatting using the CHAR(13) Function

Question

Viewing 10 posts - 1 through 9 (of 9 total)

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

ron k SSCertifiable Points: 6103 More actions · Answer 1

Here's the example from Books Online but even this doesn't work when I test it!?

This example uses CHAR(13) to print name, address, and city information on separate lines, when the results are returned in text.

USE Northwind
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address,    
       + CHAR(13) + City, + Region 
FROM EmployeesWHERE EmployeeID = 1

Here is the result set:

Nancy Davolio
507 - 20th Ave. E.
Apt. 2A
Seattle            WA

Note In this record, the data in the Address column also contains a control character.

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Try this:

DECLARE @RoomDescription VarChar(100) (increase len if needed)

SELECT @RoomDescription =

ServiceID, FolderID, 'Accomodation for' + ' ' + RTRIM(Title) + ' ' + RTRIM(PaxFirstName) + ' ' + RTRIM(PaxLastName) + CHAR(13)

+ 'At:' + ' ' + CAST(ISNULL(HotelName, '') AS varchar) + ' ' + 'For' + ' ' + CAST(ISNULL(NoOfPax, 0) AS varchar) + ' ' + 'Person(s)' + CHAR(13)

+ 'Check in Date:' + ' ' + CONVERT(Char(10), DateIn, 103) + CHAR(13) + 'Check out Date:' + ' ' + CONVERT(Char(10), DateOut, 103) + CHAR(13)

+ RTRIM(RoomType) + ',' + ' ' + RateBasis AS [Service Description], RoomSell

FROM dbo.tblAccomodation

PRINT @RoomDescription

Maybe you could create an UDF to modify to suit your purposes!

**ASCII stupid question, get a stupid ANSI !!!**

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

I use CHAR(13) all the time. Can you show us and example of your output?

Thanks - that is odd....

I wasn't born stupid - I had to study.

Sherif Maamoun Ten Centuries Points: 1254 More actions · Answer 4

Here is an example of what I get when I run a report to create invoice based on the above view. As you see it is not properly formatted?

Accomodation for Mr. Sherif MaamounAt: 0 For

2 Person(s)Check in Date: 01/04/2005Check

out Date: 03/04/2005Single, Half

Board

Sherif Maamoun Ten Centuries Points: 1254 More actions · Answer 5

Thanks but when I try to run it I get the following error message:

Server: Msg 141, Level 15, State 1, Line 9

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

Sherif - I suggested that you create an UDF or stored procedure to use the t-sql and return the string...is that doable ?!

**ASCII stupid question, get a stupid ANSI !!!**

WSquared SSCrazy Points: 2393 More actions · Answer 7

Hi,

Try replacing the CHAR(13) with a CHAR(13) + CHAR(10)

Sherif Maamoun Ten Centuries Points: 1254 More actions · Answer 8

Ten Centuries

Points: 1254

May 6, 2005 at 7:11 am

This worked great, thank you very much

Sherif Maamoun Ten Centuries Points: 1254 More actions · Answer 9

Can you tell me how as I can use this at a lot of situations in my program