Post reply

Problem with formatting using the CHAR(13) Function

  • May 5, 2005 at 11:01 am

    #362531

    I am trying to concatenate mutliple columns into a single column using CHAR(13) to force new line but it does not work as I get a strange character at its place. Here is the view I am trying to create:

    SELECT     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

  • May 5, 2005 at 1:11 pm

    #557208

    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.

     

  • May 5, 2005 at 1:39 pm

    #557214

    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 !!!**

  • May 5, 2005 at 3:21 pm

    #557233

    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.

  • May 5, 2005 at 11:52 pm

    #557270

           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

  • May 6, 2005 at 12:08 am

    #557272

    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.

  • May 6, 2005 at 4:56 am

    #557302

    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 !!!**

  • May 6, 2005 at 6:39 am

    #557324

    Hi,

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

  • May 6, 2005 at 7:11 am

    #557339

    This worked great, thank you very much

  • May 6, 2005 at 7:12 am

    #557341

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

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

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