Query help

• vijay.singh 97987

  SSC-Addicted

  Points: 495

  More actions

  October 25, 2010 at 3:15 am

  #230356

  Hi All,

  Need a help from you guys!

  I have a column by name Amount and the data is stored in Numeric.

  i want to display it in below format.

  EX:

  Amount

  ---------

  1100 to be displayed as 1,100

  2200 to be displayed as 2,200

  Thanks in Advance.:-)

• hallidayd

  SSCrazy

  Points: 2490

  More actions

  October 25, 2010 at 4:15 am

  #1241468

  This can be done in SQL, but note that the result is no longer a number. You would be best served doing this in your presentation layer. To return a string of numbers with commas at every third number:

  SELECT SUBSTRING(CONVERT(VARCHAR(10), CONVERT(MONEY, 12345), 1), 1, CHARINDEX('.', CONVERT(VARCHAR(10), CONVERT(MONEY, 12345), 1)) - 1)

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  October 25, 2010 at 4:17 am

  #1241469

  It might not be the answer you'd like to hear, but you should not do this on the database side. Do it in the app side instead.

  If you insist doing this with T-SQL, you can take a look at CONVERT.

  It could be something like

  CONVERT(varchar(20),CAST(Amount AS money),1)

  If you don't want the decimal part, you can use REPLACE to get rid of it:

  REPLACE(CONVERT(varchar(20),CAST(Amount AS money),1),'.00',char(0))

  -- Gianluca Sartori

• vijay.singh 97987

  SSC-Addicted

  Points: 495

  More actions

  October 25, 2010 at 4:27 am

  #1241473

  Hi,

  Thanks For the Quick reply.:-):-)

  The below code also works fine which is from MSDN.

  SELECT CONVERT(VARCHAR,CAST(25000000 AS MONEY) ,1)

  Thanks and Regards,

  Vijay Singh