Converting Float to Varchar

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[1]','varbinary(128)'))) as val
from @x.nodes('*') as x(n)

I am pretty sure this will solve your problem.

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

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.

If the accuracy of FLOAT is the issue, have you considered using DECIMAL/NUMERIC instead?