Hidden Formatting Troubles with STR() (SQL Spackle)

Question

Post reply

Hidden Formatting Troubles with STR() (SQL Spackle)

Jeff Moden

SSC Guru

Points: 1004356
More actions
December 14, 2010 at 8:30 pm

#232469

Comments posted to this topic are about the item Hidden Formatting Troubles with STR() (SQL Spackle)
--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 15 posts - 1 through 15 (of 80 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

I've hardly used STR(), so I didn't know about all of these pitfalls. Thanks for the Spackle!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Aye. Thanks Wayne. After I found out (the hard way, for sure) about some of the faults of STR(), I don't use it anymore... unless I'm extremely caffeine depraved. 😛

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

Carlo Romagnano SSC-Insane Points: 22670 More actions · Answer 3

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.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 4

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.

Thanks

nigel. SSChampion Points: 11636 More actions · Answer 5

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 ...?" 🙂

Nigel
How to post questions

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 6

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.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 7

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 SSCrazy Eights Points: 8218 More actions · Answer 8

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.

Nadrek SSC-Insane Points: 20039 More actions · Answer 9

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.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 10

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 SSC Guru Points: 1004356 More actions · Answer 11

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.

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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

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.

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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

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.

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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

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.

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)