Hidden Formatting Troubles with STR() (SQL Spackle)

  • paul.goldstraw (1/7/2014)


    Thanks Jeff, makes sense. If someone asked me to right justify some text I think the quicker solution would be closer to what I would have attempted anyway as i'm more used to using those sorts of string manipulations, but at least now I know for sure it's the better implementation

    Thanks

    Paul

    That being said, I'll take that as a suggestion and modify the article to use spaces at first as a direct replacerment for STR() and then expose the ability to use other characters and the reasons why one might want to do so.

    Thanks for the feedback, Paul.

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

  • Jeff Moden (1/7/2014)


    paul.goldstraw (1/7/2014)


    Thanks Jeff, makes sense. If someone asked me to right justify some text I think the quicker solution would be closer to what I would have attempted anyway as i'm more used to using those sorts of string manipulations, but at least now I know for sure it's the better implementation

    Thanks

    Paul

    That being said, I'll take that as a suggestion and modify the article to use spaces at first as a direct replacerment for STR() and then expose the ability to use other characters and the reasons why one might want to do so.

    Thanks for the feedback, Paul.

    Sounds like that's above and beyond the call of a Spackle article! 😛


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • There's something missing that I don't see in your article nor the Microsoft website.

    If you convert bigint value 9223372036854775807 to float, the exact value of the result is 9223372036854775808.  This is the nearest value that can be expressed in binary with 53 significant bits. SQL Server is doing this correctly. But STR(*,19) produces '9223372036854775800'.  What we're not being told is that the value is being rounded to 17 significant digits before adding trailing zeros.  The final result is off by 8 from the original number, but the conversion to float is only responsible for 1 of those.

    The same with the second bigint value, 922337203685477580.  The nearest value that can be stored in a float is 922337203685477632, and SQL Server is correctly making that conversion.  But STR(*,19) rounds to 17 significant digits and then adds a trailing zero.  The result is '922337203685477630'.  In this case the conversion to float added 52, but after rounding the final result is off by 50.

    This is perfectly reasonable. Consider this code:

    declare @F float = 1e30

    select STR(@F,31)

    The exact value of @F is 1000000000000000019884624838656, the closest value to 10^30 that can be expressed with 53 significant bits.  STR outputs '1000000000000000000000000000000' as you would expect.

     

  • If I'm reading your post correctly, I actually DO demonstrate that phenomena in the article in a couple of places.

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

  • Fun fact:  Your article reveals an error in how SQL Server casts numeric to float.

    STR(.922337203685477580,19,19)   should end in 776, but it ends with 777.

    Further investigation shows that cast(.922337203685477580 to float) is off by one bit.   Weirdly, cast(.92233720368547758 to float), without the trailing zero, works properly.

    It looks like the bug has been fixed.  I get the error in SQL Server 2012 and 2014, but I get the correct values in 2017.

Viewing 5 posts - 76 through 80 (of 80 total)

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