Post reply

new line syntax

  • September 14, 2006 at 11:14 am

    #113572

    Is there a way to get a new line within a SQL string?

    'some text' + (what do I need here for a new line?) + ' more text'

    Thanks!

  • September 14, 2006 at 11:24 am

    #660473

    CHAR(10) + CHAR(13)


    * Noel

  • September 14, 2006 at 12:41 pm

    #660497

    I'm getting an error "invalid operator for data type. Operator equals add, type equals text". Here is my text within a view:

     , t2.ProgIntro + CHAR(10) + CHAR(13) + 'Right ' + .....

  • September 14, 2006 at 12:52 pm

    #660500

    This is because you may have one of the column with text datatype

    try converting text to varchar datatype

    select convert(varchar,textcol)+char(13)+' Right' from tab

  • September 14, 2006 at 1:18 pm

    #660503

    successful, thanks!!

  • September 15, 2006 at 3:17 am

    #660636

    Note: Be careful when you are using VARCHAR without specifying a length. BOL states that when casting/converting VARCHAR, without a length, defaults to length=30. When declaring a variable of VARCHAR type, it to only a VARCHAR(1).

    These two things are going to bite you if you not always actively think about it. (If you do as the above example, CONVERT a TEXT column to VARCHAR, you will probably loose data since I find it hard to believe that you would always store less then 30 characters in a TEXT column)

    I would recommend always specifying the length when using CHAR or VARCHAR data.

    BOL (topic called 'char and varchar'):

    Fixed-length (char) or variable-length (varchar) character data types.

    char[(n)]

    Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.

    varchar[(n)]

    Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.

    Remarks

    When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

    (...)

Viewing 6 posts - 1 through 5 (of 5 total)

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