Hi guys, I need to convert float values to varchar. I'm using STR function to do it. But it is not accurate. See code: --example A DECLARE @float FLOAT SET @float = -32.55650315 SELECT STR(@float,50,16) --Example B DECLARE @float FLOAT SET @float = -32.55650315 SELECT STR(@float,50,8) In Example A, the result is wrong for me. In Example B, the result is correct for me. But I don't know how many decimal places there to put in the STR function. Does anyone have any idea to resolve it? Thanks
Ok, the problem here seems to be that you need to store a float value in an xml fragment. You cannot store an accurate string representation of a float accurately every time as the conversion to a string will introduce errors. Thinking around this problem, I thought that maybe what was required was another way of storing the float that did not involve error introducing conversions...maybe storing the binary data of the float would be good? Yes but that needs converting to "xml safe" characters - easily handled already by your use of BINARY BASE64. The problem then is converting back again - you cannot convert directly from varbinary to float. So, maybe one intermediate step is required...convert to a DECIMAL first. Try this: declare @floats table ( number float ); insert into @floats select -312542.55650315 UNION ALL select -2487870.08284 UNION ALL select 0 UNION ALL select 0.000 UNION ALL select 0.00001 UNION ALL select 48 declare @x xml set @x = ( -- first convert to a suitable decimal (you can choose the accuracy required by your data -- bearing in mind that too few decimal places will introduce errors) then convert to -- varbinary - this will then get encoded as base64 in the xml select convert(varbinary(128),convert(decimal(37,20),number)) as encoded_float from @floats as floats for xml auto,elements,binary base64 ) -- check the xml - to see the encoded value select @x -- next show how to convert back from base64 to varbinary to decimal and finally back to float -- note that the decimal used here must match exactly the one used to encode it earlier select convert(float,convert(decimal(37,20),n.value('./encoded_float','varbinary(128)'))) as val from @x.nodes('*') as x(n) I am pretty sure this will solve your problem.
It's not so much that the STR function isn't accurate--the float data type itself is not accurate, as you have shown above. The best you could do, given using floats, may be to use the length of the float as a way of trying to limit floating point rounding error: declare @floats table ( number float ); insert into @floats select -32.55650315 UNION ALL select -20.08284 UNION ALL select 0 UNION ALL select 0.000 UNION ALL select 0.00001 UNION ALL select 48 select LEN(number), STR(number, 50, LEN(number)), STR(number, 50, 16) from @floats; The second column is the "correct" one, in that it reduces your risk of rounding errors.
Why do you use str function to convert your floats? str does not do just conversion, it does much more instead. - First, it allocates as many characters to the final result as you specify for the second parameter (50 in your case). - Then it converts the input so it has as many digits past the decimal point as you specify in the third parameter. In your case when you pass 16, the number exceeds the maximum allowed significant digits of FLOAT which happens to be 15, so you get approximated result, not exact. Float is not always suitable for calculations anyway because it is approximate type not exact. - Then str calculates how many characters the result has and left pads it with as many spaces as needed until the total number of characters is equal to whatever you passed as the secon parameter (50 in your case). I don't know why you perceive the results of your example A as invalid, but if you reduce the value of the third parameter to the str function so it does not exceed the number of digits of the input and also decrease the value of the second parameter from 50 to something what makes sense then you should get your numbers converted to the string and right-justified (left padded with spaces). If you don't want your results to be right-justified or in other words you don't want the whole bunch of extra spaces appended to the left of your result then you can either change your input to some normal, exact data type, such as decimal of suitable precision and scale and then you can simply cast it as varchar so you always get consistent results. To answer you original question, the thirs parameter to the str function must not exceed 15 minus the number of digits before the decimal point when you use float. This is due to the float limit of 15 digits in total. This means: SELECT STR(@float,50,13) -- this is OK SELECT STR(@float,50,14) -- this is already too much because 14 + 2 > 15 Oleg