Hidden Formatting Troubles with STR() (SQL Spackle)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jeff Moden » Hidden Formatting Troubles with STR() (SQL...

43 posts, Page 1 of 5

1 2 3 4 5 »»»

Hidden Formatting Troubles with STR() (SQL Spackle)

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Tuesday, December 14, 2010 8:30 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1034868

WayneS

Posted Tuesday, December 14, 2010 10:55 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:38 PM
Points: 6,368, Visits: 8,230

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

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

Jeff Moden

Posted Tuesday, December 14, 2010 11:30 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

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. Tongue

--Jeff Moden

Post #1034901

Carlo Romagnano

Posted Wednesday, December 15, 2010 1:29 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:54 AM
Points: 1,969, Visits: 1,820

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

Hardy21

Posted Wednesday, December 15, 2010 2:15 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:42 PM
Points: 877, Visits: 1,158

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

nigel.

Posted Wednesday, December 15, 2010 2:19 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 1,163, Visits: 2,601

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

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

Jason-299789

Posted Wednesday, December 15, 2010 2:44 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:38 AM
Points: 803, Visits: 2,123

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

Samuel Vella

Posted Wednesday, December 15, 2010 4:19 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 03, 2013 2:54 AM
Points: 328, Visits: 1,848

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

Scott Arendt

Posted Wednesday, December 15, 2010 6:59 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:52 AM
Points: 3,044, Visits: 1,256

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

Nadrek

Posted Wednesday, December 15, 2010 7:09 AM

Say Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 12:28 PM
Points: 675, Visits: 2,031

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 »

43 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions