Formatting Select Columns

  • Hi All!

    Can anyone give me an assit with correctly formatting my column calls in a select statement? 

    Details:

    Lets say I got a column in a table called SalesName with a length of 50.  Well, in my select statement I want to rename the column and return only 25 positions from the field.  How do I format the TSQL syntax for this? 

     I could only get as far as the following without throwing an error.

    select SalesName "Name" from CommERV where ProcessDate = '1/1/05'

    and RevDate = '1/1/05'

    and TIN = '2042416'

    TIA!

    -MJ

  • SELECT

       SUBSTRING(SalesName,1,25) As Name

    FROM

       CommERV

    WHERE

       ProcessDate = '1/1/05'

       AND RevDate = '1/1/05'

       AND TIN = '2042416'

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • Here's a couple of other ways...

    SELECT

       LEFT(SalesName,25) As Name

    FROM

       CommERV

    WHERE

       ProcessDate = '1/1/05'

       AND RevDate = '1/1/05'

       AND TIN = '2042416'

    SELECT

       CAST(SalesName AS VARCHAR(25)) As Name

    FROM

       CommERV

    WHERE

       ProcessDate = '1/1/05'

       AND RevDate = '1/1/05'

       AND TIN = '2042416'

    The latter of the two actually does a better job of limiting the column length for output purposes.  I dunno if LEFT is any faster than SUBSTRING but it's less to type.

     

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

Viewing 3 posts - 1 through 2 (of 2 total)

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