Hidden Formatting Troubles with STR() (SQL Spackle)

  • Jeff Moden

    SSC Guru

    Points: 994667

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • WayneS

    SSC Guru

    Points: 95341

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

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Carlo Romagnano

    SSC-Insane

    Points: 21793

    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

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

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

  • Jason-299789

    SSC-Insane

    Points: 21601

    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

    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

    SSCertifiable

    Points: 7671

    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

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

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

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 994667

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 994667

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 994667

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code 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