x

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

more ▼

asked Jul 06, 2011 at 11:42 AM in Default

Rafael Krisller gravatar image

Rafael Krisller
13 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.
more ▼

answered Jul 06, 2011 at 02:56 PM

Mister Magoo gravatar image

Mister Magoo
1.8k 2 3 5

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.
Jul 08, 2011 at 09:52 AM Rafael Krisller
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jul 06, 2011 at 12:23 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Very well done, sir.
Jul 06, 2011 at 12:26 PM Kevin Feasel

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'
Jul 06, 2011 at 12:57 PM Rafael Krisller
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jul 06, 2011 at 12:01 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

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.
Jul 06, 2011 at 12:32 PM Rafael Krisller
@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.
Jul 06, 2011 at 12:39 PM Oleg
@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.
Jul 06, 2011 at 12:47 PM Oleg
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.
Jul 06, 2011 at 12:49 PM Kevin Feasel
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).
Jul 06, 2011 at 01:17 PM Rafael Krisller
(comments are locked)
10|1200 characters needed characters left
If the accuracy of FLOAT is the issue, have you considered using DECIMAL/NUMERIC instead?
more ▼

answered Jul 06, 2011 at 12:07 PM

AaronBertrand gravatar image

AaronBertrand
905 1 3

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).
Jul 06, 2011 at 01:53 PM Rafael Krisller
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x29

asked: Jul 06, 2011 at 11:42 AM

Seen: 4114 times

Last Updated: Jul 06, 2011 at 11:56 AM