I need to convert float values to varchar. I'm using STR function to do it. But it is not accurate.
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?
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.
I am pretty sure this will solve your problem.
answered Jul 06, 2011 at 02:56 PM
Why do you use str function to convert your floats? str does not do just conversion, it does much more instead.
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:
answered Jul 06, 2011 at 12:23 PM
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:
The second column is the "correct" one, in that it reduces your risk of rounding errors.
answered Jul 06, 2011 at 12:01 PM
If the accuracy of FLOAT is the issue, have you considered using DECIMAL/NUMERIC instead?
answered Jul 06, 2011 at 12:07 PM