Select convert to 2 decimal places, comma, right aligned

  • Tania Mofflin

    Ten Centuries

    Points: 1021

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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

  • dbalmf

    SSCrazy

    Points: 2063

    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]

  • chad 62627

    Old Hand

    Points: 330

    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 🙂

  • Jeff Moden

    SSC Guru

    Points: 997124

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ScottPletcher

    SSC Guru

    Points: 98555


    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • chad 62627

    Old Hand

    Points: 330

    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.

  • Jeff Moden

    SSC Guru

    Points: 997124

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • chad 62627

    Old Hand

    Points: 330

    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 🙂

  • Jeff Moden

    SSC Guru

    Points: 997124

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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