7

I have a question about this fiddle:

DECLARE @TestVal AS float = 8.88;
SELECT flt = @TestVal
    , xml = (SELECT Value = @TestVal FOR XML PATH(''), TYPE)
    , fmt17 = FORMAT(@TestVal, 'G17')
    , fmt = FORMAT(@TestVal, 'G')
    , cst = CAST(@TestVal AS nvarchar(50))
    , fmt17_roundtrip = CAST(FORMAT(@TestVal, 'G17') AS float)
    , fmt_roundtrip = CAST(FORMAT(@TestVal, 'G') AS float)
    , cst_roundtrip = CAST(CAST(@TestVal AS nvarchar(50)) AS float)
;

https://dbfiddle.uk?rdbms=sqlserver_2019&fiddle=0cf05f882eb24f53e9484f043af99446

I was having trouble with some XML going out by default in scientific notation which, while not incorrect or inaccurate, isn't terribly readable.

I was originally using FORMAT(floatcol, 'G17') because of the comments on this documentation page that:

Note that, when used with a Double value, the "G17" format specifier ensures that the original Double value successfully round-trips. This is because Double is an IEEE 754-2008-compliant double-precision (binary64) floating point number that gives up to 17 significant digits of precision. We recommend its use instead of the "R" format specifier, since in some cases "R" fails to successfully round-trip double-precision floating point values. The following example illustrates one such case.

Well, today I found that it seems to add some extra insignificant digits to the string. In this particular example, they all "round trip" fine, but the G17 format has an extra insignificant digit.

And despite the fact that it might not affect me on a round-trip, I really don't want to be sending this to another party with extra digits.

Right now I am leaning towards changing to FORMAT('G'), but am not sure of the implications of that. Currently the format string used in these XML exports is a configuration setting stored in my system, so it's easiest to continue to use FORMAT since it requires no code changes.

So before I go testing on a variety of other values, my question is what is the difference between G and G17 overall and what problems might I run into using G instead of G17?

(Yes, this value needs to be a float, not a decimal or integer or currency, and the domain of the float can vary by different measurements/contexts).

System does need to run on 2012 and up.

3
  • Have you thought about just rounding the number to the decimal you need
    – nbk
    Jul 7, 2020 at 15:41
  • @nbk the range of the float can vary by measurement - in this particular use case this is a fact table of different cardiovascular measurements - so there is an open question of what different measurements' requirements of precision are - like body mass in kg would be 1DP, heart rate would be 0 DP, a particular blood vessel diameter measurement might be 2DP. It definitely never goes out to 17 sig figs - these aren't astronomical measurements, and accuracy of imaging and other systems is limited.
    – Cade Roux
    Jul 8, 2020 at 16:13
  • look every valier has it s define dgits , so you can save that in a table,a refer to it in your table. so you have for every ctagory the right amiunt if digits. the main problem of yours are fractals and so use Round to get rid of them
    – nbk
    Jul 8, 2020 at 16:27

1 Answer 1

6

my question is what is the difference between G and G17 overall and what problems might I run into using G instead of G17?

Sorry don't know about that but you could try to do something with the XML. Not sure if this something you could use but here it is anyway.

When dealing with typed XML you will not get the scientific notation for your value, you will get it for other values. So use typed XML and assign the resulting XML to a XML variable bound to the schema using xs:double for double precision.

Here is a schema.

create xml schema collection SC_F as '  
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="F" type="xs:double">
    </xs:element>
</xs:schema>
';  

And a query using the schema.

declare @TestVal float = 8.88;
declare @X xml(SC_F);
set @X = (select @TestVal as F for xml path(''), type);
select @X;

Result:

<F>8.88</F>

Another way to fix it with XML is to get the float value into XML and then use the XML as a value to the XML query. Now that sounds more complicated than it is, I hope.

declare @TestVal float = 8.88;
select cast('' as xml).query('sql:variable("@TestVal")');

Result

8.88

Here is a little bit of code showing the different output you get for different solutions. Depending on values you will eventually get the scientific notation for all methods.

declare @T table(F float not null);

insert into @T(F) values(8.88),(0),(0.0001),(0.00001),(0.000001),
  (0.0000001),(0.84551240822557006);

declare @X1 xml;
declare @X2 xml(SC_F);
declare @X3 xml;
declare @X4 xml;
declare @X5 xml;

set @X1 = (select F from @T for xml path(''));
set @X2 = (select F from @T for xml path(''));
set @X3 = (select cast('' as xml).query('sql:column("F")') as F from @T for xml path(''));
set @X4 = (select format(F, 'G') as F from @T for xml path(''));
set @X5 = (select format(F, 'G17') as F from @T for xml path(''));

select @X1 as ScienceAllTheWay, 
       @X2 as UsingASchema, 
       @X3 as XMLTrickery,
       @X4 as FormatG,
       @X5 as FormatG17;

Results:

ScienceAllTheWay

<F>8.880000000000001e+000</F>
<F>0.000000000000000e+000</F>
<F>1.000000000000000e-004</F>
<F>1.000000000000000e-005</F>
<F>1.000000000000000e-006</F>
<F>1.000000000000000e-007</F>
<F>8.455124082255701e-001</F>

UsingASchema

<F>8.88</F>
<F>0</F>
<F>0.0001</F>
<F>1E-05</F>
<F>1E-06</F>
<F>1E-07</F>
<F>0.84551240822557006</F>

XMLTrickery

<F>8.88</F>
<F>0.0E0</F>
<F>0.0001</F>
<F>0.00001</F>
<F>0.000001</F>
<F>1.0E-7</F>
<F>0.84551240822557</F>

FormatG

<F>8.88</F>
<F>0</F>
<F>0.0001</F>
<F>1E-05</F>
<F>1E-06</F>
<F>1E-07</F>
<F>0.84551240822557</F>

FormatG17

<F>8.8800000000000008</F>
<F>0</F>
<F>0.0001</F>
<F>1.0000000000000001E-05</F>
<F>9.9999999999999995E-07</F>
<F>9.9999999999999995E-08</F>
<F>0.84551240822557006</F>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.