STR function

  • It's friday morning and i'm not sure if i'm going mad.

    PRINT STR(98.43075,28,14) returns 98.43075000000000

    PRINT STR(98.43075,28,15) returns 98.430750000000003

    Can somebody please explain.

    Thanks.

  • You added an extra decimal place and that was just enough to highlight the rounding issues associated with the Numeric data type, which is what STR assumes.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil,

    Thanks for that.

    I forgot to mention that the variable is declared as DECIMAL(28,15) so I am surprised it causes a rounding issue.

    I've never come across this issue before and would seem a major problem for a lot of people. I'm basically writing a script to search for numeric values across all fields where the dat type, precision and scale is the same without having to convert backwards and forwards.

    I could understand if you were reducing the number of decimal places on a very long number as that would be a normal rounding issue but on a number with only 5 deciaml places I would expect when you use STR that it just fills the extra places with zero's.

    I've tested this in VB.net and VBScript within SSIS and it gives the intended result so it would seem to only affect TSQL.

    Why would it allow the STR function to go to a max of 16 deciaml places if it starts throwing a wobbly at 15?

    Using SQL 2005 SP3 (4035).

    thanks.

  • jasonmorris (1/20/2012)


    Phil,

    Thanks for that.

    I forgot to mention that the variable is declared as DECIMAL(28,15) so I am surprised it causes a rounding issue.

    I've never come across this issue before and would seem a major problem for a lot of people. I'm basically writing a script to search for numeric values across all fields where the dat type, precision and scale is the same without having to convert backwards and forwards.

    I could understand if you were reducing the number of decimal places on a very long number as that would be a normal rounding issue but on a number with only 5 deciaml places I would expect when you use STR that it just fills the extra places with zero's.

    I've tested this in VB.net and VBScript within SSIS and it gives the intended result so it would seem to only affect TSQL.

    Why would it allow the STR function to go to a max of 16 deciaml places if it starts throwing a wobbly at 15?

    Using SQL 2005 SP3 (4035).

    thanks.

    My initial thoughts were that the STR function is not to blame - rather the fact that numeric rounding issues are affecting the underlying value of the variable and that STR was just truncating that at 15dp to make everything look as you would expect.

    But when I tried to prove that hypothesis, I could not, so now I am puzzled along with you 🙂 Hopefully someone else will chime in with ideas.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Chiming in...

    http://www.sqlservercentral.com/articles/T-SQL/71565/

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

  • Thanks Jeff

    I checked that BOL entry out this morning, then tried stuff like this:

    declare @x decimal(28,15) = 98.43075

    declare @y float

    set @y = @x

    select @x Dec, @y Float

    in the expectation of being able to replicate the STR() behaviour, but could not. Any ideas?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Jeff thanks for the link but I am using DECIMAL which is one of the recommendations.

    So are we saying that the STR function doesn't work and therefore should be avoided?

    I can't see why this is a rounding issue as there is nothing being rounded. My collegue ran the same code and it appended a 2 to the end!!

  • Ah, this gives the dodgy result:

    select @x Dec, @y Float, cast(@y as numeric(28,15)) casted

    So looks like it may be cast as float and then back to numeric in STR()

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Anybody else have any ideas before I post on the MSDN forums?

  • I explained the behaviour of STR() (or so I thought).

    For your purposes, it is not suitable. If instead you cast your variables as, say, varchar(30) and then print them, you should find that all is well.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 10 posts - 1 through 9 (of 9 total)

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