format numbers as strings with commas

• Joe Hocker

  Grasshopper

  Points: 20

  More actions

  March 30, 2006 at 4:11 pm

  #189936

  I’m looking to query a integer value and display it as a formatted string (varchar) with commas. For instance the value stored in the database is 12345, I would like to query this value as a formatted string like 12,345.

   

  I’ve see this asked on other threads, but I don’t see a good answer.

   

  I expect some one will say it is not the job of the server to do this. Well, I am in a situation the returned value is expected to be a string.

• Paul Mu

  SSCertifiable

  Points: 5547

  More actions

  March 30, 2006 at 4:37 pm

  #629993

  This seems to work:

  select convert(varchar,cast(12345 as money),1)

• Mike Kendall

  SSC Eights!

  Points: 958

  More actions

  March 31, 2006 at 9:16 am

  #630171

  To drop the decimal point and what follows.

  select

  left(convert(varchar,cast(12345 as money),1),len(convert(varchar,cast(12345 as money),1))-3)

  And yes, someone will tell you it's not the servers job.  Doesn't change the fact that it's your job.... 

  Mike

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  April 1, 2006 at 7:40 pm

  #630291

  Finally... the voice of reason...

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