asked
Converting Float to Varchar
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[1]','varbinary(128)'))) as val from @x.nodes('*') as x(n) I am pretty sure this will solve your problem.
Thanks Mister Magoo. It's work fine. But I kept the old way. Because when you work with floats you should consider loss of accuracy.
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.
@Rafael Krisller Your example is yet another proof that using floats is not a very good idea. You can still get away with using the formula I suggested (15 minus digits before decimal point) but it would be very cumbersome just to convert the number to string. Using decimal could be a better thing to do.
Thanks Kevin, But see example: 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 select LEN(number), STR(number, 50, LEN(number)), STR(number, 50, 16) from @floats; The LEN function returns lenght of field and not count of digits.
@Kevin Feasel It does not get rid of them. The best to avoid the float related errors is by using 15 - number of digits before the decimal point formula. It is still subjected to the 15 digits float limitation but at least there are no problems associated with the len function applied to float which has a maximum value of 8 no matter how big or small is the actual float value.
You are both right--the above is wrong. And I agree on the correct answer of using decimal types rather than trying to fuss with floats and dealing with the inherent inaccuracy of the data type.
I know using decimal could be a better thing to do. But I can't do it now. Because in the system, the users are who set the accuracy of the field. So, I can't limit it with Decimal(38,x).
If the accuracy of FLOAT is the issue, have you considered using DECIMAL/NUMERIC instead?
I can't use Decimal fields because in the system, the users are who set the accuracy of the field. So, I can't limit it with Decimal(38,x).
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
All right! I'll explain why I use STR function to convert my floats. I've got a XML LOG for all actions (insert, delete, update) in my tables. But my XML field stored float values for short. 9.109999999999999e+000 = 9,11 In this method I lose accuracy in some cases and when I need to do rollback command I'll return a wrong value. Declare @x as XML Select @x = (Select Inserted.[id], CASE WHEN ISNULL(Inserted.[vlr_float],0) = ISNULL(Deleted.[vlr_float],0) THEN NULL ELSE Inserted.vlr_float END AS vlr_float, Deleted.[id], CASE WHEN ISNULL(Inserted.[vlr_float],0) = ISNULL(Deleted.[vlr_float],0) THEN NULL ELSE Deleted.vlr_float END AS vlr_float from Inserted left join Deleted on Inserted.[id] = Deleted.[id] FOR XML AUTO, TYPE, BINARY BASE64 , ELEMENTS ABSENT, Root('ROOT')) Insert Auditoria(tabela,texto_XML,acao) Select 'Acao',@x,'U'
