STR function

Question

STR function

jasonmorris

SSCrazy

Points: 2492
More actions
January 20, 2012 at 2:19 am

#254026

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.

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

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jasonmorris SSCrazy Points: 2492 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

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)

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jasonmorris SSCrazy Points: 2492 More actions · Answer 6

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 7

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jasonmorris SSCrazy Points: 2492 More actions · Answer 8

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 9

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel