Formatting Select Columns

• MJ-227602

  SSC Rookie

  Points: 39

  More actions

  April 22, 2005 at 9:28 am

  #164245

  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

• Dinakar Nethi-176633

  SSCarpal Tunnel

  Points: 4689

  More actions

  April 22, 2005 at 10:10 am

  #554378

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

• Jeff Moden

  SSC Guru

  Points: 1004592

  More actions

  April 23, 2005 at 9:24 pm

  #554492

  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)