Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hidden Formatting Troubles with STR() (SQL Spackle)


Hidden Formatting Troubles with STR() (SQL Spackle)

Author
Message
Rich Mechaber
Rich Mechaber
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 3661
Thanks Jeff, for your customary focused, useful style and content!

<math_complaint>
Your article points out a secondary annoyance here in SQL: the ROUND() function returns a value of the same type as its passed argument. In your examples under "Incorrect Rounding", note that all of the returned values using ROUND(SomeNumber, 2) return 3 decimals.

In junior high school, if I answered the test question "Round off 0.325 to 2 decimal places" with "0.330" I would have gotten that one wrong. Significant digits? What's that? That trailing zero means something in science, and its inclusion here is, IMHO, incorrect. The correct answer is 0.33.
</math_complaint>

Any thoughts on this?

Rich
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
rmechaber (12/15/2010)
Thanks Jeff, for your customary focused, useful style and content!

<math_complaint>
Your article points out a secondary annoyance here in SQL: the ROUND() function returns a value of the same type as its passed argument. In your examples under "Incorrect Rounding", note that all of the returned values using ROUND(SomeNumber, 2) return 3 decimals.

In junior high school, if I answered the test question "Round off 0.325 to 2 decimal places" with "0.330" I would have gotten that one wrong. Significant digits? What's that? That trailing zero means something in science, and its inclusion here is, IMHO, incorrect. The correct answer is 0.33.
</math_complaint>

Any thoughts on this?

Rich


Thanks for the feedback. You're one of the few to pick up on the fact that the explicit ROUNDing to two decimal places didn't actually change the underlying datatype nor format the output. And, I agree... it's one of those things that just isn't expected.

For those that didn't notice, ROUND didn't and doesn't do any formatting or conversion of the underlying datatype. It simply does what it says... it Rounds to a given number of decimal places (or whole number places if you use a minus-length). Because of the original multiplication by 1/1000th, the underlying datatype has 3 decimal places and ROUND doesn't change that. The number is simply rounding with no change in format and no change in datatype.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
Carlo Romagnano (12/15/2010)
I always use this this syntax:
RIGHT('0000000000' + CAST(SomeNumber AS VARCHAR(10)),10)

Because I didn't know that STR() is also used for formatting, I thought it only converts string to number.


I do similar although usually with leading spaces when I want to right justify for output to a file that requires it (or something similar). Thanks for the feedback and for posting your example. :-)

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
Hardy21 (12/15/2010)
Nice article.
I have checked STR() usage in my application but usage is safe because we are not dealing with more than 12 digit number.


Very cool. I love that kind of feedback where folks have actually gone back to check. Well done and thanks.:-)

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
nigel. (12/15/2010)
Nice little eye opener Jeff, thanks.

I particularly like :

...formatting data in SQL Server is your basic "Bozo-no-no"


Can I quote that the next time I see another question like:

"How do I get TSQL to format my datetime column to show just the date/time/whatever ...?" :-)


Absolutely but don't say "never" in the process because "It Depends". It's usually a horrible idea to burn clock cycles formatting in SQL Server for way too many reasons to list here (including local display settings in a global environment) but there IS the occasional justifiable need... sometimes there is no GUI to do the formatting for you especially if you're writing directly to a file.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
Nadrek (12/15/2010)
Hardy21 (12/15/2010)
Nice article.
I have checked STR() usage in my application but usage is safe because we are not dealing with more than 12 digit number.


May I suggest that if numeric integrity is truly important to your application, you either:

A) switch over to a deterministic method of converting from the current nondeterministic one

or

B) Run a comprehensive test (i.e. verify that every single number from 0 to 999999999999 does, indeed, return what you expect when you use the STR() function), probably from the largest and most risky number to the smallest, ensure all STR() use cannot end up in 13 chars or more, and comment all STR() code with this note, a short description of the flaw, and a link to Jeff's article. You don't want some bright-eyed person later on copying this "perfectly fine, working production code" later for something larger, or updating it with future business requirements.
B1) Repeat your tests every upgrade... just in case.

I expect nondeterministic functions to be nondeterministic. If I want reliability, I choose something that's expected/known/documented/empirically shown to be deterministic.


Heh... obviously B) is the method to avoid. :-D Thanks for the feedback, Nadrek.

@Hardy21... Nadrek is absolutely correct. A lot of people "leverage" existing code for other things which is why you'll see me get mad as hell when someone jusifies a RBAR method of coding on this forum just because their particular instance is supposedly "guaranteed" to not grow beyond a certain number of rows. If the usage of STR() is easy to find in your case, it's probably pretty easy to change, as well. You already know from the article that changing it from STR() will actually be a bit of a benefit in the area of performance.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
Jason Lees-299789 (12/15/2010)
Like others Ive always used the CAST or Convert functions for converting numbers to strings and so didnt know the pitfalls of using the STR() function so it was a useful example.


Samuel Vella (12/15/2010)
Looks like I'm another one who's never used the STR() function and having read the article I think I'll keep it that way.
At least I have a good reason now and a reference, so thanks!


Scott Arendt (12/15/2010)
Thanks Jeff. I always learn something when I read your articles. I don't use STR() and I don't think I'll start now.


Thanks for the feedback folks. I really appreciate it especially when someone posts that they learned something new or useful. :-)

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
rmechaber (12/15/2010)
Thanks Jeff, for your customary focused, useful style and content!


BTW... thanks for the comment above. I've had several folks tell me in the past that I write "too simple" or "too direct". Steve Jones suggested some "single problem" articles such as are in this "spackle" series and my goal is to keep things short, sweet, and direct because not all of us have the time to read a 26 page article every morning. :-)

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 2673
Jeff Moden (12/15/2010)
nigel. (12/15/2010)
Nice little eye opener Jeff, thanks.

I particularly like :

...formatting data in SQL Server is your basic "Bozo-no-no"


Can I quote that the next time I see another question like:

"How do I get TSQL to format my datetime column to show just the date/time/whatever ...?" :-)


Absolutely but don't say "never" in the process because "It Depends". It's usually a horrible idea to burn clock cycles formatting in SQL Server for way too many reasons to list here (including local display settings in a global environment) but there IS the occasional justifiable need... sometimes there is no GUI to do the formatting for you especially if you're writing directly to a file.


Some shops make extensive use of SQL Server agent jobs to output files directly (as unreliable as agent job failure detection and reporting is, it's still better than Windows Scheduler); and RTRIM() formatting saves considerable network traffic when your database was built by a vendor who didn't understand that VARCHAR() existed. Also, some languages don't really have formatting (most interactive voice response/telephony systems are an oddity in many ways).

Formatting in SQL also has advantages in that multiple platforms and languages and programs receive consistent formatting.
Tom Bakerman
Tom Bakerman
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 930
Nice article Jeff. You would think, that after all this time, some computer scientist somewhere would come up with a decent way to deal with strings. It seems that every language comes with a big Surgeon General's warning "String manipulation may be hazardous to your application".
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search