Post reply

Select convert to 2 decimal places, comma, right aligned

  • May 29, 2014 at 8:25 pm

    #307778

    Hello

    The field below currently has an output that looks like this -

    select

    A.[User_Info_Currency_1] as [Replacement Cost]

    from EQSA0345 A

    Replacement Cost

    523.88000

    60689.76000

    48860.26000

    77239.26000

    132227.26000

    65668.18000

    71523.34000

    I would like the output to be right aligned, 2 decimal places and have a comma as below

    Replacement Cost

    523.88

    60,689.76

    48,860.26

    77,239.26

    132,227.26

    65,668.18

    71,523.34

    Is there a list of all the decimal formats? I have found one which lists all the different date formats but can't find any for decimals.

    I have found many other forum posts on this topic but they all give an output not quite in the format I need, I would like to learn myself the different formats rather than just copying someone else's code.

    http://www.sql-server-helper.com/tips/date-formats.aspx">

    http://www.sql-server-helper.com/tips/date-formats.aspx

    I also understand it's best to format the raw data outside of sql which I usually do but this is an exception.

    Thankyou

  • May 30, 2014 at 3:05 am

    #1717691

    Formatting is usually best done in the front end tool.

    Is this for a report?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 30, 2014 at 4:07 am

    #1717703

    I agree this should be done in the frontend app.

    This code will give you the commas and decimal places

    SELECT CONVERT(VARCHAR, CAST('1234567890' AS MONEY),1)

    As for left or right justification, that will depend on how you are presenting the data to the end user

    ============================================================
    David

    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • January 3, 2019 at 3:34 pm

    #2017932

    Okay look...I know this is 5 years later...but I googled this issue, and this forum post is the #2 item in the google search results.
    I just figured out one way to do this, and I thought I would throw it in here for anyone else who also performs the same google search as me.

    This won't work for everyone. I have my management studio set to use a fixed width font for my grid results. If you're not using a fixed width font, it will look funky.

    You can use STR() to right justify numbers, but if you want to add commas, then that causes issues. And this just occurred to me and it works great:

    SELECT REVERSE(CONVERT(CHAR(10), REVERSE(FORMAT(COUNT(*),'N'))))

    NOTE: Due to the use of two REVERSE functions and the FORMAT function, this is not recommended for use on queries that will produce a large number of rows. I'm personally using this for a query that outputs a small number of rows (<5000 rows) so for me, the performance hit is not a problem.

    I'm adding commas using the FORMAT() function, reversing it, then converting to a CHAR(10) to pad spaces, then reversing again. This will produce a right justified number with commas, but only if you are using a fixed width font on your grid results.

    Hope one day this helps someone 🙂

  • January 3, 2019 at 10:07 pm

    #2017948

    chad 62627 - Thursday, January 3, 2019 3:34 PM

    Okay look...I know this is 5 years later...but I googled this issue, and this forum post is the #2 item in the google search results.
    I just figured out one way to do this, and I thought I would throw it in here for anyone else who also performs the same google search as me.

    This won't work for everyone. I have my management studio set to use a fixed width font for my grid results. If you're not using a fixed width font, it will look funky.

    You can use STR() to right justify numbers, but if you want to add commas, then that causes issues. And this just occurred to me and it works great:

    SELECT REVERSE(CONVERT(CHAR(10), REVERSE(FORMAT(COUNT(*),'N'))))

    I'm adding commas using the FORMAT() function, reversing it, then converting to a CHAR(10) to pad spaces, then reversing again. This will produce a right justified number with commas, but only if you are using a fixed width font on your grid results.

    Hope one day this helps someone 🙂

    Just so you know, FORMAT is an average of 44 times slower than cast or convert, Using 2 reversals isn't going to help performance there, either.

    You also don't want to use STR() for similar reasons.

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

  • January 4, 2019 at 10:51 am

    #2018023


    select
        right(space(12)+convert(varchar(20),cast([User_Info_Currency_1] as money),1), 12) as [Replacement Cost]
    from (
    values(
    523.88000),(
    60689.76000),(
    48860.26000),(
    77239.26000),(
    132227.26000),(
    65668.18000),(
    71523.34000)
    ) as EQSA0345(User_Info_Currency_1)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 8, 2019 at 2:02 pm

    #2018349

    Jeff Moden - Thursday, January 3, 2019 10:07 PM

    Just so you know, FORMAT is an average of 44 times slower than cast or convert, Using 2 reversals isn't going to help performance there, either.

    You also don't want to use STR() for similar reasons.

    Yes, I'm aware it's much slower. But for my personal uses, speed wasn't an issue as I was only dealing with a couple thousand rows. I just wanted to post up my findings in case it might help someone else who was looking for a similar solution.

  • January 8, 2019 at 6:45 pm

    #2018370

    chad 62627 - Tuesday, January 8, 2019 2:02 PM

    Jeff Moden - Thursday, January 3, 2019 10:07 PM

    Just so you know, FORMAT is an average of 44 times slower than cast or convert, Using 2 reversals isn't going to help performance there, either.

    You also don't want to use STR() for similar reasons.

    Yes, I'm aware it's much slower. But for my personal uses, speed wasn't an issue as I was only dealing with a couple thousand rows. I just wanted to post up my findings in case it might help someone else who was looking for a similar solution.

    You KNEW of the performance problem and posted it anyway? .

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

  • January 9, 2019 at 10:01 am

    #2018437

    Jeff Moden - Tuesday, January 8, 2019 6:45 PM

    You KNEW of the performance problem and posted it anyway? .

    Yes, like I said, for my personal uses, it wasn't a problem. It's only a problem if you're using it on a large number of rows. I'm using this for a query I occasionally run to grab some quick statistics and it outputs a small number of rows..

    I'll edit my post and note the performance issue if it will make you happy as I probably should have noted it anyways 🙂

  • January 10, 2019 at 9:19 am

    #2018558

    chad 62627 - Wednesday, January 9, 2019 10:01 AM

    Jeff Moden - Tuesday, January 8, 2019 6:45 PM

    You KNEW of the performance problem and posted it anyway? .

    Yes, like I said, for my personal uses, it wasn't a problem. It's only a problem if you're using it on a large number of rows. I'm using this for a query I occasionally run to grab some quick statistics and it outputs a small number of rows..

    I'll edit my post and note the performance issue if it will make you happy as I probably should have noted it anyways 🙂

    I guess my point is... why would you write something that has a known performance issue just because you're using a smaller number of rows?  If you write enough of such code, then the system dies of a thousand cuts like the ones I've been fixing in the legacy code for the companies that I work for.

    If it's worth doing, it's worth doing right... all the time.

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

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

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