Add $ symbol to column values

• vigneshkumart50

  Ten Centuries

  Points: 1205

  More actions

  February 12, 2014 at 9:39 pm

  #285579

  Hi,

  I want to add $ symbol to column values and convert the column values to western number system

  Column values

  Dollar

  4255

  25454

  467834

  Expected Output:

  $ 4,255

  $ 25,454

  $ 467,834

  My Query:

  select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application

  COST is the int datatype and needs to be changed.

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 12, 2014 at 10:38 pm

  #1688598

  Try this

  DECLARE @dollar TABLE (id INT IDENTITY(1,1),Cost INT)

  INSERT INTO @dollar

  ( Cost )

  VALUES (-4255),(25454),(467834)

  select ID, GETDATE() as 'Date', REPLACE('$' + CONVERT(VARCHAR(20),CONVERT(MONEY,SUM(Cost))),'$-','-$') Dollars

  , REPLACE('$' + CONVERT(VARCHAR(20),MAX(CONVERT(MONEY,COST))),'$-','-$') Funding

  from @dollar

  GROUP BY ID;

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 12, 2014 at 10:43 pm

  #1688600

  If you were to be using SQL 2012 or 2014, then this would work

  DECLARE @dollar TABLE (id INT IDENTITY(1,1),Cost INT)

  INSERT INTO @dollar

  ( Cost )

  VALUES (-4255),(25454),(467834)

  select ID, GETDATE() as 'Date'

  , FORMAT(SUM(Cost), 'C', 'en-us') AS Dollars

  ,FORMAT(MAX(Cost),'C','fr') AS Funding --fr to see euros/french, en-gb to see pounds/UK

  FROM @dollar

  GROUP BY ID;

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• vigneshkumart50

  Ten Centuries

  Points: 1205

  More actions

  February 12, 2014 at 11:55 pm

  #1688607

  It worked. Thanks a lot.

• VSSGeorge

  SSCrazy Eights

  Points: 8146

  More actions

  February 13, 2014 at 2:59 am

  #1688643

  Thanks SQLRNNR, Very informative.

  Please explain the difference of usage of REPLACE in your first post.

  Also, why you used '$-','-$' in the query.

• David Burrows

  SSC Guru

  Points: 65138

  More actions

  February 13, 2014 at 6:23 am

  #1688697

  Junglee_George (2/13/2014)

  ---

  Thanks SQLRNNR, Very informative.

  Please explain the difference of usage of REPLACE in your first post.

  Also, why you used '$-','-$' in the query.

  It is to move the minus sign to the left of the dollar sign

  ie changes $-4255.00 to -$4255.00

  Far away is close at hand in the images of elsewhere.
  Anon.

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 13, 2014 at 8:26 am

  #1688773

  David Burrows (2/13/2014)

  ---

  Junglee_George (2/13/2014)

  ---

  Thanks SQLRNNR, Very informative.

  Please explain the difference of usage of REPLACE in your first post.

  Also, why you used '$-','-$' in the query.

  It is to move the minus sign to the left of the dollar sign

  ie changes $-4255.00 to -$4255.00

  Thanks for following up on that.:cool:

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 13, 2014 at 8:28 am

  #1688775

  Junglee_George (2/13/2014)

  ---

  Thanks SQLRNNR, Very informative.

  Please explain the difference of usage of REPLACE in your first post.

  Also, why you used '$-','-$' in the query.

  You are welcome.

  David answered the $ question. I am uncertain what you mean by "difference of usage of replace." Could you clarify? Or was it just about the $? The replace is necessary to help with the position of the $ as David pointed out.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 13, 2014 at 8:29 am

  #1688778

  vigneshkumart50 (2/12/2014)

  ---

  It worked. Thanks a lot.

  You're welcome

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• David Burrows

  SSC Guru

  Points: 65138

  More actions

  February 13, 2014 at 8:40 am

  #1688782

  SQLRNNR (2/13/2014)

  ---

  David Burrows (2/13/2014)

  ---

  Junglee_George (2/13/2014)

  ---

  Thanks SQLRNNR, Very informative.

  Please explain the difference of usage of REPLACE in your first post.

  Also, why you used '$-','-$' in the query.

  It is to move the minus sign to the left of the dollar sign

  ie changes $-4255.00 to -$4255.00

  Thanks for following up on that.:cool:

  Your welcome 🙂

  About time I contributed something useful for a change 😉

  Far away is close at hand in the images of elsewhere.
  Anon.