Add $ symbol to column values

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

  • 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

  • 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

  • It worked. Thanks a lot.

  • Thanks SQLRNNR, Very informative.

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

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

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

  • 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

  • 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

  • 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

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

Viewing 10 posts - 1 through 9 (of 9 total)

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