Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Hidden Formatting Troubles with STR() (SQL Spackle) Expand / Collapse
Author
Message
Posted Tuesday, December 14, 2010 8:30 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:54 PM
Points: 41,212, Visits: 38,517
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1034868
Posted Tuesday, December 14, 2010 10:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, August 22, 2016 8:23 AM
Points: 5,863, Visits: 10,234
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1034893
Posted Tuesday, December 14, 2010 11:30 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:54 PM
Points: 41,212, Visits: 38,517
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1034901
Posted Wednesday, December 15, 2010 1:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 10, 2016 7:48 AM
Points: 3,258, Visits: 3,092
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.
Post #1034940
Posted Wednesday, December 15, 2010 2:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:03 AM
Points: 1,134, Visits: 1,399
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
Post #1034965
Posted Wednesday, December 15, 2010 2:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 15, 2016 8:28 AM
Points: 1,276, Visits: 2,818
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
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #1034970
Posted Wednesday, December 15, 2010 2:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 16, 2016 5:39 AM
Points: 1,076, Visits: 3,225
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
Post #1034988
Posted Wednesday, December 15, 2010 4:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 11, 2016 2:34 AM
Points: 351, Visits: 2,136
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!
Post #1035012
Posted Wednesday, December 15, 2010 6:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 25, 2016 6:36 AM
Points: 4,030, Visits: 1,702
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.
Post #1035101
Posted Wednesday, December 15, 2010 7:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 3, 2016 12:36 PM
Points: 917, Visits: 2,666
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.
Post #1035117
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse