Hidden Formatting Troubles with STR() (SQL Spackle)

Question

Hidden Formatting Troubles with STR() (SQL Spackle)

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

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

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.

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

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

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

David Dubois Right there with Babe Points: 763 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

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.

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

David Dubois Right there with Babe Points: 763 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

David Dubois wrote:

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.

Sorry for the very late reply. There have been some "notification" outages and your reply may have occurred during one of those.

Thanks for the post about what you've found. To summarize it, it's another great reason to avoid the STR() function.

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